Need row to column Conversion In Sql Server

Question

Orginal Data Like This Slno No Name Class Section Date Status 1 1 Raj B.com A 2017-07-01 L 2 1 Raj B.com A 2017-07-02 L 3 1 Raj B.com A 2017-07-03 P 4 1 Raj B.com A 2017-07-04 OD 5 1 Raj B.com A 2017-07-05 AB 6 2 Pothi B.com A 2017-07-01 AB 7 2 Pothi B.com A 2017-07-02 L 8 2 Pothi B.com A 2017-07-03 P 9 2 Pothi B.com A 2017-07-04 OD 10 2 Pothi B.com A 2017-07-05 AB Need To Convert like This No Name Day1 Day2 Day3 Day4 Day5 1 Raj L L P OD AB 2 Pothi AB L P OD AB

Midhun T P · Answer

Hi Try below query - DECLARE @cols AS NVARCHAR( MAX ) @ccols AS NVARCHAR( MAX ) @query AS NVARCHAR( MAX ) select @cols = STUFF(( SELECT '' + QUOTENAME([ date ]) from dummytable2 group by [ date ] FOR XML PATH( '' ) TYPE ).value( '.'  'NVARCHAR(MAX)' ) 11 '' ) select @ccols = STUFF(( SELECT '' + QUOTENAME([ date ]) + ' AS Day' + convert ( VARCHAR (20)row_number() OVER ( ORDER BY [ DATE ])) from dummytable2 group by [ date ] FOR XML PATH( '' ) TYPE ).value( '.'  'NVARCHAR(MAX)' ) 11 '' ) PRINT @ccols set @query = N 'SELECT [no][name]' + @ccols + N' from ( select [ no ][ name ]status [ date ] from dummytable2 ) x pivot ( max (status) for [ date ] in ( ' + @cols + N' ) ) p ORDER By [ NO ]' exec sp_executesql @query;