如何从这种格式转换表：
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
