How to pivot table with T-SQL?
14,338
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
Results:
id column_AAA column_BBB column_CCC
1 1AAA 1BBB 1CCC
2 2AAA 2BBB 2CCC
3 3AAA 3BBB 3CCC
Author by
DevUser
Updated on June 26, 2022Comments
-
DevUser about 2 years
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 ------------------------------------------
To this format with T-sql ?
Id |Column_AAA|Column_BBB|Colunm_CCC| ------------------------------------- 1 |1AAA |1BBB |1CCC | ------------------------------------- 2 |2AAA |2BBB |2CCC | ------------------------------------- 3 |3AAA |..... |3CCC |