How to expand a table using T-SQL?

Question

How to convert a 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 ------------------------------------------ 

In this format with T-sql?

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

Answer 1

this should give you the desired results.

 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