Pivot table without knowing row values

Question

This is my table - Select ItemName, ItemDescription from MyTable

I do not know what the values would be in ItemName or ItemDescription.

Would it be possible to display the data so that ItemName would be created as columns and the ItemDescription be the row value?

Answer 1

Requires mssql 2008+

create table mytable( ItemName varchar(10), ItemDescription varchar(10))

insert mytable values('item 1', 'desc1')
insert mytable values('item 2', 'desc2')
insert mytable values('item 1', 'desc2')

declare @list varchar(2000)
;with cte 
as(
select distinct top 100 percent replace(replace(itemname, ']', ''), '[', '') itemname
from mytable
order by itemname
)
select @list = coalesce(@list + ',' + '['+itemname+']', '['+itemname+']') from cte

declare @sql varchar(4000)
set @sql =
'
select * from
(
select replace(replace(itemname, '']'', ''''), ''['', '''') itemname, ItemDescription, row_number() over (partition by ItemName order by ItemDescription) id from mytable
)  a
PIVOT (MAX([ItemDescription]) FOR [itemname] IN ('+@list+')) AS pvt'

exec (@sql)

Result:

id  item 1  item 2
1   desc1   desc2
2   desc2   NULL