如何使用 T-SQL 透视表? [英] How to pivot table with T-SQL?

问题描述

如何从这种格式转换表格:

How to convert table from this format:

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转这个格式?

To this format with T-sql ?

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

推荐答案

这应该会给你想要的结果.

this should give you the results you want.

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

这篇关于如何使用 T-SQL 透视表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！