tsql – 如何使用T-SQL透视表？

如何从这种格式转换表：

Id | Data           |Section
------------------------------------------
1  |1AAA            |AAA
------------------------------------------
1  |1BBB            |BBB
------------------------------------------
1  |1CCC            |CCC
------------------------------------------
2  |2AAA            |AAA
------------------------------------------
2  |2BBB            |BBB
------------------------------------------
2  |2CCC            |CCC
------------------------------------------
3  |3AAA            |AAA
------------------------------------------
3  |3CCC            |CCC
------------------------------------------

用T-sql这种格式？

Id |Column_AAA|Column_BBB|Colunm_CCC|
-------------------------------------
1  |1AAA      |1BBB      |1CCC      |   
-------------------------------------
2  |2AAA      |2BBB      |2CCC      |       
-------------------------------------
3  |3AAA      |.....     |3CCC      |

解决方法

CREATE TABLE #temp
(
    id int,data varchar(50),section varchar(50)
)
insert into #temp values(1,'1AAA','AAA')
insert into #temp values(1,'1BBB','BBB')
insert into #temp values(1,'1CCC','CCC')
insert into #temp values(2,'2AAA','AAA')
insert into #temp values(2,'2BBB','BBB')
insert into #temp values(2,'2CCC','CCC')
insert into #temp values(3,'3AAA','AAA')
insert into #temp values(3,'3BBB','BBB')
insert into #temp values(3,'3CCC','CCC')

select id,[AAA] as Column_AAA,[BBB] as Column_BBB,[CCC] as Column_CCC
from 
(
    select id,data,section
    from #temp
) x
PIVOT
(
    max(data)
    FOR section IN([AAA],[BBB],[CCC])
) as p

drop table #temp

结果：

id  column_AAA column_BBB   column_CCC
1   1AAA        1BBB        1CCC
2   2AAA        2BBB        2CCC
3   3AAA        3BBB        3CCC

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!