欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2023-12-15 09:32:34
复制代码 代码如下: --行转列问题 --建立測試環境 create table test (dates varchar(6), empno varchar(5), sty...
复制代码 代码如下:

--行转列问题
--建立測試環境
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

就可以了

上一篇:

下一篇: