0

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
)
Improve this question
1
  • 1
    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. Commented Dec 2, 2013 at 12:34

1 Answer 1

Reset to default
3

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments