April 2, 2018

Sreekanth B

How to Convert Columns values into Rows in SQL Server 2014

How to Convert Columns values into Rows in SQL Server 2014?


DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(shift_date) 
                from dbo.tbl_Multi_Shift_Employee WHERE Shift_Date BETWEEN CONVERT(DATE, '01/02/2018 00:00:00',103) AND CONVERT(DATE, '28/02/2018 00:00:00',103)
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

set @query = 'SELECT Employee_ID, total, Department_ID, ' + @cols + ' from 
         (
How to Convert Columns values into Rows in SQL Server 2014            select count(*) over(partition by t.Employee_ID) total,
              s.Department_ID,
              t.Employee_ID,
              t.shift_id,
                t.shift_date
            from dbo.tbl_Multi_Shift_Employee t
            inner join dbo.tbl_department s
              on t.Department_ID = s.Department_ID
        ) x
        pivot 
        (
            count(shift_id)
            for shift_date in (' + @cols + ')
        ) p '
PRINT @query
execute(@query)

Subscribe to get more Posts :