Hello,

i would work in 3 steps to simulate the excel pivot.

1. As there are 2 row headers and an extra one for the measuretype 'value', i would group the base data to a temp table, filtered on the data you need

2. The columns have variable names (the available calenderdates), so first I would build the string with the columnnames to display

3. with the prepared data it's easier to use the PIVOT-function to get the result :

-- Step 1

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)

DECLARE @ColumnName AS NVARCHAR(MAX)

declare @FromDate date = '8/8/2016'

declare @ToDate date = '8/12/2016'

CREATE TABLE #T (EmployeeName varchar(250), PayCodeName varchar(250),

[Values] varchar(50), CalendarDate date, Hours decimal(10,2) PRIMARY KEY(EmployeeName,PayCodeName,CalendarDate,[Values]))

INSERT INTO #T

SELECT EmployeeName, PayCodeName, 'Sum of STHours', Calendardate, SUM(STHours)

FROM UT_WEEKLY_DATA

WHERE Calendardate > @FromDate AND Calendardate <= @ToDate

GROUP BY EmployeeName, PayCodeName, Calendardate

union all

SELECT EmployeeName, PayCodeName, 'Sum of OTHours', Calendardate, SUM(OTHours)

FROM UT_WEEKLY_DATA

WHERE Calendardate > @FromDate AND Calendardate <= @ToDate

GROUP BY EmployeeName, PayCodeName, Calendardate

union all

SELECT EmployeeName, PayCodeName, 'Sum of DTHours', Calendardate, SUM(DTHours)

FROM UT_WEEKLY_DATA

WHERE Calendardate > @FromDate AND Calendardate <= @ToDate

GROUP BY EmployeeName, PayCodeName, Calendardate

-- Step 2

SELECT @ColumnName= ISNULL(@ColumnName + ',','')

+ QUOTENAME(Calendardate)

FROM (SELECT DISTINCT Calendardate FROM #T) AS Courses

--Step 3: Dynamic pivot-query

SET @DynamicPivotQuery =

N'SELECT EmployeeName, PayCodeName, [Values], ' + @ColumnName + '

FROM #T PIVOT(SUM(Hours)

FOR Calendardate IN (' + @ColumnName + ')) AS PVTTable'

--Execute the Dynamic Pivot Query

EXEC sp_executesql @DynamicPivotQuery

DROP TABLE #T