SQL 横转竖 、竖专横 列转行 行转列 表旋转 （转载）

一、 数据库行列转换
转载来源:https://www.cnblogs.com/luowende2012/archive/2012/07/19/2599436.html
1、行转列
问题：假设有张学生成绩表(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 课程 
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    

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

2、列转行：

/* 
即表结构和数据为： 
姓名 语文 数学 物理 
张三 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 end 

drop table tb 

 

二、数据库行列转换 ：PIVOT 方法

https://www.cnblogs.com/OpenCoder/p/6668882.html

https://blog.csdn.net/yx183/article/details/82423420

SELECT <非透视的列>,
    [第一个透视的列] AS <列名称>,
    [第二个透视的列] AS <列名称>,
    ...
    [最后一个透视的列] AS <列名称>,
FROM
    (<生成数据的 SELECT 查询>)
    AS <源查询的别名>
PIVOT
(
    <聚合函数>(<要聚合的列>)
FOR
[<包含要成为列标题的值的列>]
    IN ( [第一个透视的列], [第二个透视的列],
    ... [最后一个透视的列])
) AS <透视表的别名>
<可选的 ORDER BY 子句>;

　　

三、使用案例

1、 静态sql

每年碳排放数据

 select 
 a.RegionID,a.RegionName,
    [2011] as C2011,[2012] as C2012,[2013] as C2013,[2014] as C2014,[2015] as C2015,[2016] as C2016,[2017] as C2017,[2018] as C2018,[2019] as C2019,[2020] as C2020,
    [2021] as C2021,[2022] as C2022,[2023] as C2023,[2024] as C2024,[2025] as C2025,[2026] as C2026,[2027] as C2027,[2028] as C2028,[2029] as C2029,[2030] as C2030,[2031] as C2031,[2032] as C2032,[2033] as C2033,[2034] as C2034,[2035] as C2035,[2036] as C2036,
    [2037] as C2037,[2038] as C2038,[2039] as C2039,[2040] as C2040,[2041] as C2041,[2042] as C2042,[2043] as C2043,[2044] as C2044,[2045] as C2045,[2046] as C2046,[2047] as C2047,[2048] as C2048,[2049] as C2049,[2050] as C2050
 from (  
 select c.DataYear,c.RegionID,t.RegionName,t.DespSeq, sum(c.GDP) as GDP
 from T_CarbonEmission as c inner join T_Region as t on  c.RegionID=t.RegionID
 where t.RegionType=1
 group by c.DataYear,c.RegionID, t.RegionName,t.DespSeq

) d
 PIVOT(sum( d.GDP) FOR d.DataYear IN([2011],[2012],[2013],[2014],[2015],[2016],[2017],[2018],[2019],[2020],[2021],[2022],[2023],[2024],[2025],[2026],[2027],[2028],[2029],[2030],[2031],[2032],[2033],[2034],[2035],[2036],[2037],[2038],[2039],[2040],[2041],[2042],[2043],[2044],[2045],[2046],[2047],[2048],[2049],[2050])) 
 AS a
order by a.DespSeq

 

 2、动态sql和动态列

1）动态sql问题

1、where in（1,2,3）问题 

https://www.cnblogs.com/mrma/p/3953094.html

https://www.cnblogs.com/shouyeren/p/6807328.html

1）动态类，数据接收问题

一种：前端获取数据后，js动态类实现横转纵，缺点是数据量大时带宽占用大。

 

一种：C#没有动态类，放弃EF，改用ADO.NET,数据库里横转纵,用DataTable接收数据，api直接返回DataTable。