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
(
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)