ms sql 行转列 统计

Question

创建如下表：
create table #t(shopid varchar(10),  fee decimal(18,4), procid varchar(10), qty int)
insert into #t
select 'AAAAA',  '30.0000' , '00001',   10
union all select 'AAAAA',  '40.0000' ,  '00002',   20
union all select 'AAAAA',  '50.0000' ,  '00003',   10
union all select 'AAAAA',  '8.0000' ,  '00004',   30
union all select 'AAAAA',  '6.0000' ,   '00001',   10
union all select 'BBBBB',  '20.0000' ,   '00002',   20
union all select 'BBBBB',  '70.0000' ,   '00003',   10
union all select 'BBBBB',  '80.0000' ,   '00005',   30
select * from #t
go我要结果：
shopid  fee  00001  00002  00003  00004  00005
 AAAAA  134   20     20     10     30      0 
 BBBBB  170   0      20     10      0      30

Answer 1

create table t(shopid varchar(10), fee decimal(18,4), procid varchar(10), qty int)
insert into t
select 'AAAAA', '30.0000' , '00001', 10
union all select 'AAAAA', '40.0000' , '00002', 20
union all select 'AAAAA', '50.0000' , '00003', 10
union all select 'AAAAA', '8.0000' , '00004', 30
union all select 'AAAAA', '6.0000' , '00001', 10
union all select 'BBBBB', '20.0000' , '00002', 20
union all select 'BBBBB', '70.0000' , '00003', 10
union all select 'BBBBB', '80.0000' , '00005', 30
go--如果procid固定为：00001 00002 00003 00004 00005用静态SQL。
select shopid ,
       sum(fee) fee,
       sum(case procid when '00001' then qty else 0 end) [00001],
       sum(case procid when '00002' then qty else 0 end) [00002],
       sum(case procid when '00003' then qty else 0 end) [00003],
       sum(case procid when '00004' then qty else 0 end) [00004],
       sum(case procid when '00005' then qty else 0 end) [00005]
from t
group by shopid
/*
shopid     fee                                      00001       00002       00003       00004       00005       
---------- ---------------------------------------- ----------- ----------- ----------- ----------- ----------- 
AAAAA      134.0000                                 20          20          10          30          0
BBBBB      170.0000                                 0           20          10          0           30（所影响的行数为 2 行）
*/--如果procid不固定用动态SQL。
declare @sql varchar(8000)
set @sql = 'select shopid ,sum(fee) fee '
select @sql = @sql + ' , sum(case procid when ''' + procid + ''' then qty else 0 end) [' + procid + ']'
from (select distinct procid from t) as a
set @sql = @sql + ' from t group by shopid'
exec(@sql) 
/*
shopid     fee                                      00001       00002       00003       00004       00005       
---------- ---------------------------------------- ----------- ----------- ----------- ----------- ----------- 
AAAAA      134.0000                                 20          20          10          30          0
BBBBB      170.0000                                 0           20          10          0           30（所影响的行数为 2 行）
*/drop table t

Answer 2

有关2005的方法和行列转换的其他内容参考如下:
/*
标题：普通行列转换(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-03-09
地点：广东深圳
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)： 
姓名 语文 数学 物理 
---- ---- ---- ----
李四 74   84   94
张三 74   83   93
-------------------
*/create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')---------------------------------/*
问题：在上述结果的基础上加平均分，总分，得到如下结果：
姓名 语文 数学 物理 平均分 总分 
---- ---- ---- ---- ------ ----
李四 74   84   94   84.00  252
张三 74   83   93   83.33  250
*/--SQL SERVER 2000 静态SQL。
select 姓名 姓名,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理,
  cast(avg(分数*1.0) as decimal(18,2)) 平均分,
  sum(分数) 总分
from tb
group by 姓名--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m , 
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')drop table tb ------------------
------------------/*
问题：如果上述两表互相换一下：即表结构和数据为：
姓名 语文 数学 物理
张三 74　　83　　93
李四 74　　84　　94
想变成(得到如下结果)： 
姓名 课程 分数 
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
*/create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go--SQL SERVER 2000 静态SQL。
select * from
(
 select 姓名 , 课程 = '语文' , 分数 = 语文 from tb 
 union all
 select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
 union all
 select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns 
where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。--------------------
/*
问题：在上述的结果上加个平均分，总分，得到如下结果：
姓名 课程   分数
---- ------ ------
李四 语文   74.00
李四 数学   84.00
李四 物理   94.00
李四 平均分 84.00
李四 总分   252.00
张三 语文   74.00
张三 数学   83.00
张三 物理   93.00
张三 平均分 83.33
张三 总分   250.00
------------------
*/select * from
(
 select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb 
 union all
 select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb
 union all
 select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb
 union all
 select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb
 union all
 select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 enddrop table tb

Answer 3

--PIVOT/UNPIVOT的用法(行列转换)　　--PIVOT的用法：　　--首先创建测试表，然后插入测试数据　　create table test(id int,name varchar(20),quarter int,profile int) 　　insert into test values(1,'a',1,1000)　　insert into test values(1,'a',2,2000)　　insert into test values(1,'a',3,4000)　　insert into test values(1,'a',4,5000)　　insert into test values(2,'b',1,3000)　　insert into test values(2,'b',2,3500)　　insert into test values(2,'b',3,4200)　　insert into test values(2,'b',4,5500)　　select * from test　　--id name quarter profile　　----------- -------------- ----------- -----------　　--1 a 1 1000　　--1 a 2 2000　　--1 a 3 4000　　--1 a 4 5000　　--2 b 1 3000　　--2 b 2 3500　　--2 b 3 4200　　--2 b 4 5500　　--(8 row(s) affected)　　--使用PIVOT将四个季度的利润转换成横向显示:　　select id,name,　　[1] as "一季度",　　[2] as "二季度",　　[3] as "三季度",　　[4] as "四季度"　　from　　test　　pivot　　(　　sum(profile)　　for quarter in　　([1],[2],[3],[4])　　)　　as pvt　　--id name 一季度 二季度 三季度 四季度　　-------- --------- ----------- -------- ------- -------　　--1 a 1000 2000 4000 5000　　--2 b 3000 3500 4200 5500　　--(2 row(s) affected)　　--UNPIVOT的用法：　　--首先建立测试表，然后插入测试数据　　drop table test　　create table test(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int)　　insert into test values(1,'a',1000,2000,4000,5000)　　insert into test values(2,'b',3000,3500,4200,5500)　　select * from test　　--id name Q1 Q2 Q3 Q4　　-------- ------- --------- --------- -------- --------　  --1 a 1000 2000 4000 5000　　--2 b 3000 3500 4200 5500　　--(2 row(s) affected)　　--使用UNPIVOT，将同一行中四个季度的列数据转换成四行数据：　　select id,name,quarter,profile　　from　　test　　unpivot　　(　　profile　　for quarter in　　([Q1],[Q2],[Q3],[Q4])　　) 　　as unpvt　　--id name quarter profile　　----------- ----------- ---------- -----------　　--1 a Q1 1000　　--1 a Q2 2000　　--1 a Q3 4000　　--1 a Q4 5000　　--2 b Q1 3000　　--2 b Q2 3500　　--2 b Q3 4200　　--2 b Q4 5500　　--(8 row(s) affected)

Answer 4

行列转换：
http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html?33238（总结帖子）
http://topic.csdn.net/u/20090912/14/25d2e1b2-f352-4713-8618-d3433ba27bef.html?99104（经典帖子）

Answer 5

SQL2005动态：create table #t(shopid varchar(10), fee decimal(18,4), procid varchar(10), qty int)
insert into #t
select 'AAAAA', '30.0000' , '00001', 10
union all select 'AAAAA', '40.0000' , '00002', 20
union all select 'AAAAA', '50.0000' , '00003', 10
union all select 'AAAAA', '8.0000' , '00004', 30
union all select 'AAAAA', '6.0000' , '00001', 10
union all select 'BBBBB', '20.0000' , '00002', 20
union all select 'BBBBB', '70.0000' , '00003', 10
union all select 'BBBBB', '80.0000' , '00005', 30
select * from #t
go
declare @s varchar(max)
select @s=ISNULL(@s+',','')+QUOTENAME(procid) from #t group by procid
exec('select * from (select shopid,fee=sum(fee)over(partition by shopid),procid,qty from #t) a pivot(max(qty) for procid in('+@s+'))b')
/*
shopid     fee                                     00001       00002       00003       00004       00005
---------- --------------------------------------- ----------- ----------- ----------- ----------- -----------
AAAAA      134.0000                                10          20          10          30          NULL
BBBBB      170.0000                                NULL        20          10          NULL        30(2 行受影响)
*/