Need row to column Conversion In Sql Server

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

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)')
,1,1,'')
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)')
,1,1,'')
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;

1 Reply

Know the answer? Post it — somebody with the same question will find it here.