August 18, 2016 at 7:15 am
Hello. I am in need of some assistance. I am trying to create a pivot view of data in SQL to replace a current method of dumping data into Excel and automating the pivot process.
Given my current SQL output (please see uploaded image), is it possible to create a dynamic pivot table similar to the desired pivot output (please see uploaded image)?
I have been trying for days using examples found on the web and no examples are getting me to the right direction...
Thank you for your time.
declare @FromDate date = '8/8/2016'
declare @ToDate date = '8/14/2016'
SELECT DISTINCT Calendar.CalendarDate, UT_Employees.EmployeeName, UT_PayCodes.PayCodeName,
(SELECT ISNULL(dbo.UTF_Weekly_Payroll_ST_Hours(@JobID,UT_Employees.EMPLOYEEID,UT_PayCodes.PayCodeID,Calendar.CalendarDate),0)) AS STHours,
(SELECT ISNULL(dbo.UTF_Weekly_Payroll_OT_Hours(@JobID,UT_Employees.EMPLOYEEID,UT_PayCodes.PayCodeID,Calendar.CalendarDate),0)) AS OTHours,
(SELECT ISNULL(dbo.UTF_Weekly_Payroll_DT_Hours(@JobID,UT_Employees.EMPLOYEEID,UT_PayCodes.PayCodeID,Calendar.CalendarDate),0)) AS DTHours,
(SELECT ISNULL(dbo.UTF_Weekly_Payroll_PDM_Units(@JobID,UT_Employees.EMPLOYEEID,UT_PayCodes.PayCodeID,Calendar.CalendarDate),0)) AS Perdiems,
(SELECT ISNULL(dbo.UTF_Weekly_Payroll_PUT_Units(@JobID,UT_Employees.EMPLOYEEID,UT_PayCodes.PayCodeID,Calendar.CalendarDate),0)) AS Trucks,
(SELECT ISNULL(dbo.UTF_Weekly_Payroll_TVL_Units(@JobID,UT_Employees.EMPLOYEEID,UT_PayCodes.PayCodeID,Calendar.CalendarDate),0)) AS Travels
FROM UT_Employees INNER JOIN
UT_JobPayrollDetails ON UT_Employees.EmployeeID = UT_JobPayrollDetails.EmployeeID INNER JOIN
UT_PayCodes ON UT_JobPayrollDetails.PayCodeID = UT_PayCodes.PayCodeID CROSS JOIN
UT_Jobs CROSS JOIN
UT_JobPayrollHeaders CROSS JOIN
Calendar
WHERE (Calendar.CalendarDate BETWEEN @FromDate AND @ToDate)
ORDER BY EmployeeName, PayCodeName, CalendarDate
August 18, 2016 at 9:18 am
If you're going to generate the pivot in excel, just query the normalized data and create the pivot there. You can create a view which could be the source for the dynamic pivot table and link it to excel to prevent copying and pasting. You could also use PowerPivot.
August 18, 2016 at 9:34 am
I know I can achieve the pivoted data result in Excel, however, my goal is to NOT use Excel. I am trying to get the same output from SQL and pass those results to a datagrid within an application.
August 18, 2016 at 9:39 am
suggest you post the create table script and insert data scripts for your sample data please.
thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 18, 2016 at 10:29 am
Please see attached. Is this suitable?
August 18, 2016 at 11:15 am
james.stanich (8/18/2016)
I know I can achieve the pivoted data result in Excel, however, my goal is to NOT use Excel. I am trying to get the same output from SQL and pass those results to a datagrid within an application.
thanks for the sample data...great.
now...can you please tell us what this datagrid can do as far as pivoting your data?
I appreciate you dont want to use Excel....but what functionality does the datagrid provide and what does it require for its dataset.
As you require "dates" as pivot columns ...can the datagrid do this automagically...or you expecting some form of dynamic SQL to provide these columns.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 18, 2016 at 11:20 am
The data would be displayed within a Telerik Winform Datagrid.
I would need the pivot to handle dynamic dates, but no more than 7 since the display is for weekly information.
I tried some examples on the web regarding dynamic SQL pivot columns and could not figure out how to get the distinct values of dates.
In your opinion, is what I am after possible in SQL?
August 18, 2016 at 11:33 am
james.stanich (8/18/2016)
The data would be displayed within a Telerik Winform Datagrid.I would need the pivot to handle dynamic dates, but no more than 7 since the display is for weekly information.
I tried some examples on the web regarding dynamic SQL pivot columns and could not figure out how to get the distinct values of dates.
In your opinion, is what I am after possible in SQL?
sorry but I am not familiar with Telerik Winform Datagrid.
you have already described your current output...so I assume that Telerik Winform Datagrid cannot provide your expected results based on your data?
how familiar are you with Telerik Winform Datagrid.....does it prescribe a template for input data?
whilst your expected results (from excel) are potentially possible, it seems to me that end formatting and report layout is required to be done in Telerik Winform Datagrid.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 19, 2016 at 1:44 pm
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply