mssql 数据库表行转列，列转行终极方案

--行转列问题
--建立測試環境
create table test
(dates varchar(6),
empno varchar(5),
stype varchar(1),
amount int)
--插入數據
insert test select '200605', '02436', 'a', 5
union all select '200605', '02436', 'b', 3
union all select '200605', '02436', 'c', 3
union all select '200605', '02436', 'd', 2
union all select '200605', '02436', 'e', 9
union all select '200605', '02436', 'f', 7
union all select '200605', '02436', 'g', 6
union all select '200605', '02438', 'a', 7
union all select '200605', '02438', 'b', 8
union all select '200605', '02438', 'c', 0
union all select '200605', '02438', 'd', 3
union all select '200605', '02438', 'e', 4
union all select '200605', '02438', 'f', 5
union all select '200605', '02438', 'g', 1
go
--測試
--如果stype固定，可以這麼寫
select
dates,
empno,
sum(case stype when 'a' then amount else 0 end) as a,
sum(case stype when 'b' then amount else 0 end) as b,
sum(case stype when 'c' then amount else 0 end) as c,
sum(case stype when 'd' then amount else 0 end) as d,
sum(case stype when 'e' then amount else 0 end) as e,
sum(case stype when 'f' then amount else 0 end) as f,
sum(case stype when 'g' then amount else 0 end) as g
from test
group by dates,empno
order by dates,empno

--如果stype不固定，用動態語句
declare @s varchar(1000)
set @s=''
select @s=@s+',sum(case stype when '''+stype+''' then amount else 0 end) as '+stype from (select distinct stype from test) a order by stype
set @s='select dates,empno'+@s+' from test group by dates,empno order by dates,empno'
exec(@s)
go
--如果被转置的是数字类型的话，应用下列语句
declare @s varchar(1000)
set @s='select dates,empno '
select @s=@s+',['+stype+']=sum(case when stype='''+stype+''' then amount else 0 end)'
from (select distinct stype from test) a order by stype
set @s=@s+' from test group by dates,empno'
exec(@s)

如果是列转行的话直接union all就可以了

例如 ：

city style color 46 48 50 52
长沙 s6mf01002 152 1 2 2 1
长沙 s6mf01002 201 1 2 2 1
上面到下面的样子
city style color size qty
长沙 s6mf01002 152 46 1
长沙 s6mf01002 152 48 2
长沙 s6mf01002 152 50 2
长沙 s6mf01002 152 52 1
长沙 s6mf01002 201 46 1
长沙 s6mf01002 201 48 2
长沙 s6mf01002 201 50 2
长沙 s6mf01002 201 52 1

select city,style,color,[46] from test
union all
select city,style,color,[48] from test
union all
select city,style,color,[50] from test
union all
select city,style,color,[52] from test

就可以了