将数据从一个表复制到另一个表

时间:2023-01-02 21:01:23

表一(table_1)复制到表二(table_2 )

 

 

 insert into table_2
select * from (select * from table_1) a pivot (max(bbbb) for aaaa in (语文,数学,物理)) b

注 1. bbbb,aaaa 为table_1中的两列;

      2. 语文,数学,物理 为table_2中的三列;

例:

table_1结构是:

姓名 科目 分数
张三 数学 33
张三 语文 44
李四 数学 33
李四 语文 44
李四 外语 44


table_2结构是:
姓名 数学 语文 外语
张三 33 44 0
李四 33 44 44

-------------------------------------------------------

第二种方式 (百度搜集得到)

 

SQL行转列

问题:有下列数据表

将数据从一个表复制到另一个表

要将其转变为:

 将数据从一个表复制到另一个表

可以看得出来,表2列是由表1的行数据转化而来的,只有姓名列是表1中的列。

一步一步的来嘛,其他的列难搞,如果表2只有1列姓名的话,那么very easy了,不就是一个简单的分组嘛。

Select 姓名 From 成绩表 Group By 姓名

 

现在sql语句的架子搭起来了,无论以后如何变化,分组是少不了的。

那么开始下一步,想这个结果集中再添加1列,多了我们不加,因为你不论是能处理语文,还是数学,还是英语列,那么其他的列只要原样照抄就可以了,顶多修改一下参数而已。

从语文列开始吧,这一列的数据都可以从表1中找到,如果我们只要“语文”这一列,那么也好写。

Sql语句疑似:

Select 姓名,case when 课程='语文' then 成绩 end From 成绩表 Group By 姓名

那就F5吧。

嗯,啊,...挂了。

赶紧看错误提示:

服务器: 消息 8120,级别 16,状态 1,行 1
列 '成绩表.成绩' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
服务器: 消息 8120,级别 16,状态 1,行 1
列 '成绩表.课程' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。

字段成绩和课程还必须在聚合函数中或Group By中,如果在Group By中呢?

  将数据从一个表复制到另一个表

数据倒是有了,可是行多了点,看来只能从聚合入手了。

到底是那个聚合?聚合有max,min,avg,sum,count,max和min首先干掉,我们不是要最大值,最小值,难道是平均值?难道是个数?难道是求和???

那就试试呗。

Select 姓名,sum(case when 课程='语文' then 成绩 end) From 成绩表 Group By 姓名

将数据从一个表复制到另一个表

嘿,真不错。

 

该有的都有了,就是没列名,好办,给个别名嘛。

Select 姓名,sum(case when 课程='语文' then 成绩 end) As '语文' From 成绩表 Group By 姓名

  将数据从一个表复制到另一个表

那么其他几列也就好办了。

充分发扬CVS精神。

 Select 姓名,
 sum(case when 课程='语文' then 成绩 end) As '语文' ,
 sum(case when 课程='数学' then 成绩 end) As '数学',
 sum(case when 课程='英语' then 成绩 end) As '英语'
 From 成绩表 Group By 姓名

 将数据从一个表复制到另一个表

 可是结果有NULL,那还不好办,你的case when then end没写完,还有else没写呢!
Select 姓名,
 sum(case when 课程='语文' then 成绩 else 0 end) As '语文' ,
 sum(case when 课程='数学' then 成绩 else 0 end) As '数学',
 sum(case when 课程='英语' then 成绩 else 0 end) As '英语'
 From 成绩表 Group By 姓名

将数据从一个表复制到另一个表

终于修成正果了。

 

真的?

当然是假的?

怎么假?

这不废话么!你现在写的Sql语句只能支持科目表中只有语文数学英语,那万一课程中加了一个叫物理的,那你不得改程序了吗?!

怎么办?凉拌。

也就是说这个Sql语句中有变化的地方在红字部分:

Select 姓名,
 sum(case when 课程='语文' then 成绩 else 0 end) As '语文' ,
 sum(case when 课程='数学' then 成绩 else 0 end) As '数学',
 sum(case when 课程='英语' then 成绩 else 0 end) As '英语'
 From 成绩表 Group By 姓名

红字部分要从哪里来?如果专心看,那么应该能从中间看出来,哪些个“As '语文'”,哪些个“课程='语文'”,其实是数据表中的值,如果仅仅要查询“语文,数学,英语”的话,那么easy啊。

Select 课程 From 成绩表 Group By 课程

可是我们要的结果是:

sum(case when 课程='语文' then 成绩 else 0 end) As '语文' ,
 sum(case when 课程='数学' then 成绩 else 0 end) As '数学',
 sum(case when 课程='英语' then 成绩 else 0 end) As '英语'


那不简单,就是些字符串嘛。

我拼,我拼拼拼。

Select ',sum(case when 课程='''+课程+ ''' then 成绩 else 0 end) As '''+课程+'''' From 成绩表 Group By 课程

结果为:

 将数据从一个表复制到另一个表

哈哈,只要将这一段和刚才的Select 姓名拼在一起就可以了。

现在麻烦来了,拼没有问题。

declare @sql nvarchar(4000)

set @sql='Select 姓名 '

Select @sql=@sql+',sum(case when 课程='''+课程+ ''' then 成绩 else 0 end) As '''+课程+''''

    From 成绩表 Group By 课程

Print @sql

这一段的结果是:

Select 姓名 ,sum(case when 课程='数学' then 成绩 else 0 end) As '数学',sum(case when 课程='英语' then 成绩 else 0 end) As '英语',sum(case when 课程='语文' then 成绩 else 0 end) As '语文'

跟我们的要求比较接近了,还要附加一点东西:

Select 姓名 ,sum(case when 课程='数学' then 成绩 else 0 end) As '数学',sum(case when 课程='英语' then 成绩 else 0 end) As '英语',sum(case when 课程='语文' then 成绩 else 0 end) As '语文' From 成绩表 Group By 姓名

现在麻烦的是,如何执行这个字符串,现在我们将sql语句放在了 @sql 这个变量中了,怎么执行?

灵机两动!

SqlServer中有个专门将字符串作为sql语句执行的存储过程---- sp_executesql。

就是它了。

最终版本:

declare @sql nvarchar(4000)

set @sql='Select 姓名 '

Select @sql=@sql+',sum(case when 课程='''+课程+ ''' then 成绩 else 0 end) As '''+课程+''''

    From 成绩表 Group By 课程

Print @sql

set @sql=@sql+' From 成绩表 Group By 姓名'

Print @sql

execute sp_executesql @sql

当然课程信息,不一定会从成绩表中获取,很有可能是一个专门的表格,比如叫“课程表”,那么就更easy了。

declare @sql nvarchar(4000)

set @sql='Select 姓名 '

Select @sql=@sql+',sum(case when 课程='''+课程+ ''' then 成绩 else 0 end) As '''+课程+''''

    From 课程表

Print @sql

set @sql=@sql+' From 成绩表 Group By 姓名'

Print @sql

execute sp_executesql @sql

功德圆满,土鳖不扛铁牛了。