如何使用T-SQL透视表? [英] How to pivot table with T-SQL?
本文介绍了如何使用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屋!
查看全文