Converting rows into columns

Question

I want to convert rows into columns, here is my table

Id Total
1  50
2  50
3  150

Basically i want this output

ID 50
1  1
2  1
3  (null)

What ive tried so far is

select *
from 
(
  select id, total
  from <table>
) src
pivot
(
  //Here is where i'm stuck...what should i write here
)

You need to use dynamic SQL or you need to explicitly list the values to pivot on to create columns. This is a common complaint as a perceived limitation of `PIVOT`. — Yuck, Dec 02 '13 at 12:34

Siva Ganesh · Answer 1 · 2013-12-02T13:19:43.223

Hi find the below solution

ceate temp table

select 1 as id,50 as total into #temp
union all

select 2 as id,50 as total
Union all
select 3 as id,150 as total

Prepare Columns declare @columns varchar(Max)

SELECT  @columns= COALESCE(@columns+',[','[')+cast( total as Varchar(max))+']' FROM     (select DISTINCT total from #temp) as xx

print @columns

execute query

EXEC ('
select *
from 
(
  select id as idd,id, total
  from #temp
) src
pivot
(
  COUNT(id) FOR total IN('+@columns+')
) as PVT
')

DROP TABLE #temp

you can generate any number columns with dynamically

1 Answers1