PIVOT Query for Dynamic format (Dynamic)

 

create table tbl_Dummy(ID int identity, ROWID int,VALUE nvarchar(100))

insert tbl_Dummy values(1,'+')

insert tbl_Dummy values(2,'IDIB000F508')

insert tbl_Dummy values(3,'IDIB')

insert tbl_Dummy values(4,'00F508')

insert tbl_Dummy values(5,'INDIABANK')

insert tbl_Dummy values(6,'FARRUKHABAD')

declare @cols nvarchar(max),@query nvarchar(max);

select @cols=STUFF((select ','+QUOTENAME([ROWID]) from tbl_Dummy for XML PATH('')),1,1,'')

print @cols

select @query='select * from (select ROWID,VALUE from tbl_Dummy)as s PIVOT(MAX(VALUE) FOR [ROWID] IN ('+@cols+')) AS pvt'

EXEC SP_EXECUTESQL @query

OUTPUT :

PIVOT Query for static format (value int)

 

--create table tbl_invoice(ID int identity, ROWID int,Amount int)

--insert tbl_invoice values(1,100)

--insert tbl_invoice values(2,300)

--insert tbl_invoice values(3,345)

--insert tbl_invoice values(4,11)

--insert tbl_invoice values(5,567)

--insert tbl_invoice values(6,543)

select * from (select id,Amount from tbl_invoice)as s PIVOT(SUM(Amount) FOR [id] IN ([1], [2],[3],[4],[5],[6]))AS pvt

OUTPUT :

PIVOT Query for static format (value varchar)

--create table tbl_Dummy(ID int identity, ROWID int,VALUE nvarchar(100))

--insert tbl_Dummy values(1,'+')

--insert tbl_Dummy values(2,'IDIB000F508')

--insert tbl_Dummy values(3,'IDIB')

--insert tbl_Dummy values(4,'00F508')

--insert tbl_Dummy values(5,'INDIABANK')

--insert tbl_Dummy values(6,'FARRUKHABAD')

select *

from (select ROWID,VALUE from tbl_Dummy)as s PIVOT(MAX(VALUE) FOR [ROWID] IN ([1], [2],[3],[4],[5],[6]))AS pvt

OUTPUT :