Auto-Generating Pivot Views
Coming back from the Sci-Fi world of AI, etc to some real world scenarios... Earlier I posted a generic stored procedure that automatically unpivots data so that columns become rows. Today, I provide the inverse capability, although not truly generic at this point, but the technique is generic.
Recently, I've been working with Microsoft System Center Configuration Management (SCCM) reporting and needed to create a pivot view of a task sequence. If you're not immersed in SCCM, the example probably won't make much sense, but the technique for generating the view may be useful for other scenarios. SCCM provides a lot, (and I mean ALOT) of views and reports out of the box and it provides the ability to create your own reports. SCCM 2007 stores all of it's data in a SQL Server 2005 database along with the views. For more about SCCM, see https://www.microsoft.com/systemcenter/configurationmanager/en/us/default.aspx. For more about creating custom reports, see https://www.microsoft.com/downloads/details.aspx?FamilyId=87BBE64E-5439-4FC8-BECC-DEB372A40F4A&displaylang=en
As much as I'd like to get into SCCM more, let's focus on the pivot view generation which is applicable to any scenario. In our scenario, we have multiple steps for which to report the status for a task sequence. A task sequence is a series of tasks to perform on a selected set of computers (known as a collection). You can think of it as a workflow, and SCCM lets you define whether a sub-task must complete successfully, or may be allowed to fail and allow the task sequence to continue. Task sequences may be re-run on a given machine.
Our business case is that we need to track how well our task sequences are doing and we want to drill down on specific sub-tasks (steps) that have meaning to our specific workflow and provide a single view of how all the sub-tasks performed within a date range.
SCCM captures this information in a view they provide called I created a view on top of this to ensure that only the distinct steps are captured per advertisement)
CREATE VIEW [dbo].[V_Step_Summary] AS
SELECT AdvertisementId, Step,
SUM(CASE WHEN ExitCode = 0 THEN 1 ELSE 0 END) AS SuccessCount,
SUM(CASE WHEN ExitCode <> 0 THEN 1 ELSE 0 END) AS FailureCount,
COUNT(*) AS AttemptCount
FROM [dbo].[v_TaskExecutionStatus]
GROUP BY AdvertisementId, Step
I also created a view for extracting the status of the steps by date since the underlying data is stored by date/time, and this is SQL 2005, so I can't just down-convert to date. I need to be able to group by date, so that the users can see the count of success/failures in a single day.
CREATE VIEW [dbo].[V_Step_Summary_ByDate] AS
SELECT TOP 100 PERCENT AdvertisementId, Step,
DATEADD(DD,DATEDIFF(DD,0,ExecutionTime),0) AS ExecutionDate,
SUM(CASE WHEN ExitCode = 0 THEN 1 ELSE 0 END) AS SuccessCount,
SUM(CASE WHEN ExitCode <> 0 THEN 1 ELSE 0 END) AS FailureCount,
COUNT(*) AS AttemptCount
FROM [dbo].[v_TaskExecutionStatus]
GROUP BY AdvertisementId, Step,
DATEADD(DD,DATEDIFF(DD,0,ExecutionTime),0)
OK, so now we have the building blocks, let's look at the views that pivot the data. We have 3 views: 1 to pivot just the success count, 1 to pivot just the failure count, and 1 to outer join both together to give us both failures and success.
Brace yourself, here are the views:
CREATE VIEW [dbo].[V_StepPivot_SuccessCount] AS SELECT
AdvertisementID, ExecutionDate, 'SuccessCount' as CountType,
[00] AS [S00],
[01] AS [S01],
[02] AS [S02],
[03] AS [S03],
[04] AS [S04],
[05] AS [S05],
[06] AS [S06],
[07] AS [S07],
[08] AS [S08],
[09] AS [S09],
[10] AS [S10],
[11] AS [S11],
[12] AS [S12],
[13] AS [S13],
[14] AS [S14],
[15] AS [S15],
[16] AS [S16],
[17] AS [S17],
[18] AS [S18],
[19] AS [S19],
[20] AS [S20],
[21] AS [S21],
[22] AS [S22],
[23] AS [S23],
[24] AS [S24],
[25] AS [S25],
[26] AS [S26],
[27] AS [S27],
[28] AS [S28],
[29] AS [S29],
[30] AS [S30],
[31] AS [S31],
[32] AS [S32],
[33] AS [S33],
[34] AS [S34],
[35] AS [S35],
[36] AS [S36],
[37] AS [S37],
[38] AS [S38],
[39] AS [S39],
[40] AS [S40],
[41] AS [S41],
[42] AS [S42],
[43] AS [S43],
[44] AS [S44],
[45] AS [S45],
[46] AS [S46],
[47] AS [S47],
[48] AS [S48],
[49] AS [S49],
[50] AS [S50],
[51] AS [S51],
[52] AS [S52],
[53] AS [S53],
[54] AS [S54],
[55] AS [S55],
[56] AS [S56],
[57] AS [S57],
[58] AS [S58],
[59] AS [S59],
[60] AS [S60],
[61] AS [S61],
[62] AS [S62],
[63] AS [S63],
[64] AS [S64],
[65] AS [S65],
[66] AS [S66],
[67] AS [S67],
[68] AS [S68]
FROM (SELECT AdvertisementID, ExecutionDate,Step,SuccessCount FROM dbo.V_Step_Summary_ByDate) p
PIVOT (SUM (SuccessCount) For Step in (
[00],
[01],
[02],
[03],
[04],
[05],
[06],
[07],
[08],
[09],
[10],
[11],
[12],
[13],
[14],
[15],
[16],
[17],
[18],
[19],
[20],
[21],
[22],
[23],
[24],
[25],
[26],
[27],
[28],
[29],
[30],
[31],
[32],
[33],
[34],
[35],
[36],
[37],
[38],
[39],
[40],
[41],
[42],
[43],
[44],
[45],
[46],
[47],
[48],
[49],
[50],
[51],
[52],
[53],
[54],
[55],
[56],
[57],
[58],
[59],
[60],
[61],
[62],
[63],
[64],
[65],
[66],
[67],
[68]
)) AS PVT
GO
CREATE VIEW [dbo].[V_StepPivot_FailureCount] AS SELECT
AdvertisementID, ExecutionDate, 'FailureCount' as CountType,
[00] AS [F00],
[01] AS [F01],
[02] AS [F02],
[03] AS [F03],
[04] AS [F04],
[05] AS [F05],
[06] AS [F06],
[07] AS [F07],
[08] AS [F08],
[09] AS [F09],
[10] AS [F10],
[11] AS [F11],
[12] AS [F12],
[13] AS [F13],
[14] AS [F14],
[15] AS [F15],
[16] AS [F16],
[17] AS [F17],
[18] AS [F18],
[19] AS [F19],
[20] AS [F20],
[21] AS [F21],
[22] AS [F22],
[23] AS [F23],
[24] AS [F24],
[25] AS [F25],
[26] AS [F26],
[27] AS [F27],
[28] AS [F28],
[29] AS [F29],
[30] AS [F30],
[31] AS [F31],
[32] AS [F32],
[33] AS [F33],
[34] AS [F34],
[35] AS [F35],
[36] AS [F36],
[37] AS [F37],
[38] AS [F38],
[39] AS [F39],
[40] AS [F40],
[41] AS [F41],
[42] AS [F42],
[43] AS [F43],
[44] AS [F44],
[45] AS [F45],
[46] AS [F46],
[47] AS [F47],
[48] AS [F48],
[49] AS [F49],
[50] AS [F50],
[51] AS [F51],
[52] AS [F52],
[53] AS [F53],
[54] AS [F54],
[55] AS [F55],
[56] AS [F56],
[57] AS [F57],
[58] AS [F58],
[59] AS [F59],
[60] AS [F60],
[61] AS [F61],
[62] AS [F62],
[63] AS [F63],
[64] AS [F64],
[65] AS [F65],
[66] AS [F66],
[67] AS [F67],
[68] AS [F68]
FROM (SELECT AdvertisementID, ExecutionDate,Step,FailureCount FROM dbo.V_Step_Summary_ByDate) p
PIVOT (SUM (FailureCount) For Step in (
[00],
[01],
[02],
[03],
[04],
[05],
[06],
[07],
[08],
[09],
[10],
[11],
[12],
[13],
[14],
[15],
[16],
[17],
[18],
[19],
[20],
[21],
[22],
[23],
[24],
[25],
[26],
[27],
[28],
[29],
[30],
[31],
[32],
[33],
[34],
[35],
[36],
[37],
[38],
[39],
[40],
[41],
[42],
[43],
[44],
[45],
[46],
[47],
[48],
[49],
[50],
[51],
[52],
[53],
[54],
[55],
[56],
[57],
[58],
[59],
[60],
[61],
[62],
[63],
[64],
[65],
[66],
[67],
[68]
)) AS PVT
CREATE VIEW [dbo].[V_StepPivot_FailureCount] AS SELECT
AdvertisementID, ExecutionDate, 'FailureCount' as CountType,
[00] AS [F00],
[01] AS [F01],
[02] AS [F02],
[03] AS [F03],
[04] AS [F04],
[05] AS [F05],
[06] AS [F06],
[07] AS [F07],
[08] AS [F08],
[09] AS [F09],
[10] AS [F10],
[11] AS [F11],
[12] AS [F12],
[13] AS [F13],
[14] AS [F14],
[15] AS [F15],
[16] AS [F16],
[17] AS [F17],
[18] AS [F18],
[19] AS [F19],
[20] AS [F20],
[21] AS [F21],
[22] AS [F22],
[23] AS [F23],
[24] AS [F24],
[25] AS [F25],
[26] AS [F26],
[27] AS [F27],
[28] AS [F28],
[29] AS [F29],
[30] AS [F30],
[31] AS [F31],
[32] AS [F32],
[33] AS [F33],
[34] AS [F34],
[35] AS [F35],
[36] AS [F36],
[37] AS [F37],
[38] AS [F38],
[39] AS [F39],
[40] AS [F40],
[41] AS [F41],
[42] AS [F42],
[43] AS [F43],
[44] AS [F44],
[45] AS [F45],
[46] AS [F46],
[47] AS [F47],
[48] AS [F48],
[49] AS [F49],
[50] AS [F50],
[51] AS [F51],
[52] AS [F52],
[53] AS [F53],
[54] AS [F54],
[55] AS [F55],
[56] AS [F56],
[57] AS [F57],
[58] AS [F58],
[59] AS [F59],
[60] AS [F60],
[61] AS [F61],
[62] AS [F62],
[63] AS [F63],
[64] AS [F64],
[65] AS [F65],
[66] AS [F66],
[67] AS [F67],
[68] AS [F68]
FROM (SELECT AdvertisementID, ExecutionDate,Step,FailureCount FROM dbo.V_Step_Summary_ByDate) p
PIVOT (SUM (FailureCount) For Step in (
[00],
[01],
[02],
[03],
[04],
[05],
[06],
[07],
[08],
[09],
[10],
[11],
[12],
[13],
[14],
[15],
[16],
[17],
[18],
[19],
[20],
[21],
[22],
[23],
[24],
[25],
[26],
[27],
[28],
[29],
[30],
[31],
[32],
[33],
[34],
[35],
[36],
[37],
[38],
[39],
[40],
[41],
[42],
[43],
[44],
[45],
[46],
[47],
[48],
[49],
[50],
[51],
[52],
[53],
[54],
[55],
[56],
[57],
[58],
[59],
[60],
[61],
[62],
[63],
[64],
[65],
[66],
[67],
[68]
)) AS PVT
GO
CREATE VIEW [dbo].[V_StepPivot_Count] AS SELECT sc.AdvertisementID, sc.ExecutionDate,
[S00], [F00],
[S01], [F01],
[S02], [F02],
[S03], [F03],
[S04], [F04],
[S05], [F05],
[S06], [F06],
[S07], [F07],
[S08], [F08],
[S09], [F09],
[S10], [F10],
[S11], [F11],
[S12], [F12],
[S13], [F13],
[S14], [F14],
[S15], [F15],
[S16], [F16],
[S17], [F17],
[S18], [F18],
[S19], [F19],
[S20], [F20],
[S21], [F21],
[S22], [F22],
[S23], [F23],
[S24], [F24],
[S25], [F25],
[S26], [F26],
[S27], [F27],
[S28], [F28],
[S29], [F29],
[S30], [F30],
[S31], [F31],
[S32], [F32],
[S33], [F33],
[S34], [F34],
[S35], [F35],
[S36], [F36],
[S37], [F37],
[S38], [F38],
[S39], [F39],
[S40], [F40],
[S41], [F41],
[S42], [F42],
[S43], [F43],
[S44], [F44],
[S45], [F45],
[S46], [F46],
[S47], [F47],
[S48], [F48],
[S49], [F49],
[S50], [F50],
[S51], [F51],
[S52], [F52],
[S53], [F53],
[S54], [F54],
[S55], [F55],
[S56], [F56],
[S57], [F57],
[S58], [F58],
[S59], [F59],
[S60], [F60],
[S61], [F61],
[S62], [F62],
[S63], [F63],
[S64], [F64],
[S65], [F65],
[S66], [F66],
[S67], [F67],
[S68], [F68]
FROM V_StepPivot_FailureCount fc
FULL OUTER JOIN V_StepPivot_SuccessCount sc
ON sc.AdvertisementId = fc.AdvertisementId
AND sc.ExecutionDate = fc.ExecutionDate
Wow, 68 columns are required - 1 for each step! That's a lot of pivoting. If you think I typed in all 68 column names, you're wrong! I generated the code using the below stored procedure. I didn't bother using SP_EXECUTESQL - just output and then manually cut and paste the output into a new query window and then run it from there. It wouldn't be that hard to put this all into a variable and then execute the variable to build the view directly from the stored proc. Note the retrieval from V_Step_Summary in order to generate the list of columns
CREATE PROCEDURE [dbo].[USP_Generate_StepPivot_Views_V3] AS
BEGIN
SET NOCOUNT ON
SELECT 'CREATE VIEW V_StepPivot_SuccessCount AS SELECT ' AS Line
UNION ALL SELECT ' AdvertisementID, ExecutionDate, ''SuccessCount'' as CountType,'
UNION ALL select distinct
(' [' + (CASE WHEN STEP < 10 THEN '0' + CONVERT(VARCHAR(1), STEP) ELSE CONVERT(VARCHAR(2),Step) END) + ']')
+
(' AS [S' + (CASE WHEN STEP < 10 THEN '0' + CONVERT(VARCHAR(1), STEP) ELSE CONVERT(VARCHAR(2),Step) END) +
(CASE WHEN STEP = (SELECT MAX(STEP) FROM V_Step_Summary) THEN ']' ELSE '],' END))
FROM dbo.V_Step_Summary
UNION ALL SELECT ' FROM (SELECT AdvertisementID, ExecutionDate,Step,SuccessCount FROM dbo.V_Step_Summary_ByDate) p'
UNION ALL SELECT ' PIVOT (SUM (SuccessCount) For Step in ('
-- This part generates the pivoted column list by selecting the distinct rows that <br>-- correspond to the pivot columns to create. The case statement is used to identify<br>-- the last step.
UNION ALL select distinct (' [' + (CASE WHEN STEP < 10 THEN '0' + CONVERT(VARCHAR(1), STEP) ELSE CONVERT(VARCHAR(2),Step) END) +<br> (CASE WHEN STEP = (SELECT MAX(STEP) FROM V_Step_Summary) THEN ']' ELSE '],' END)) FROM dbo.V_Step_Summary
UNION ALL SELECT ' )) AS PVT'
UNION ALL SELECT 'GO'
UNION ALL SELECT 'CREATE VIEW V_StepPivot_FailureCount AS SELECT ' AS Line
UNION ALL SELECT ' AdvertisementID, ExecutionDate, ''FailureCount'' as CountType,'
UNION ALL select distinct
(' [' + (CASE WHEN STEP < 10 THEN '0' + CONVERT(VARCHAR(1), STEP) ELSE CONVERT(VARCHAR(2),Step) END) +']')
+
(' AS [F' + (CASE WHEN STEP < 10 THEN '0' + CONVERT(VARCHAR(1), STEP) ELSE CONVERT(VARCHAR(2),Step) END) +
(CASE WHEN STEP = (SELECT MAX(STEP) FROM V_Step_Summary) THEN ']' ELSE '],' END))
FROM dbo.V_Step_Summary
UNION ALL SELECT ' FROM (SELECT AdvertisementID, ExecutionDate,Step,FailureCount FROM dbo.V_Step_Summary_ByDate) p'
UNION ALL SELECT ' PIVOT (SUM (FailureCount) For Step in ('
-- This part generates the pivoted column list by selecting the distinct rows that <br>-- correspond to the pivot columns to create. The case statement is used to identify<br>-- the last step.
UNION ALL select distinct (' [' + (CASE WHEN STEP < 10 THEN '0' + CONVERT(VARCHAR(1), STEP) ELSE CONVERT(VARCHAR(2),Step) END) +<br> (CASE WHEN STEP = (SELECT MAX(STEP) FROM V_Step_Summary) THEN ']' ELSE '],' END)) FROM dbo.V_Step_Summary
UNION ALL SELECT ' )) AS PVT'
UNION ALL SELECT 'GO'
UNION ALL SELECT 'CREATE VIEW V_StepPivot_Count AS SELECT sc.AdvertisementID, sc.ExecutionDate, '
UNION ALL select distinct
(' [S' + (CASE WHEN STEP < 10 THEN '0' + CONVERT(VARCHAR(1), STEP) ELSE CONVERT(VARCHAR(2),Step) END) + '],'
+
(' [F' + (CASE WHEN STEP < 10 THEN '0' + CONVERT(VARCHAR(1), STEP) ELSE CONVERT(VARCHAR(2),Step) END)
+
(CASE WHEN STEP = (SELECT MAX(STEP) FROM V_Step_Summary) THEN ']' ELSE '],' END)))
FROM dbo.V_Step_Summary
UNION ALL SELECT ' FROM V_StepPivot_FailureCount fc'
UNION ALL SELECT ' FULL OUTER JOIN V_StepPivot_SuccessCount sc '
UNION ALL SELECT ' ON sc.AdvertisementId = fc.AdvertisementId'
UNION ALL SELECT ' AND sc.ExecutionDate = fc.ExecutionDate'
UNION ALL SELECT 'GO'
END
OK, so you ask "how long did it take you to write that stored procedure"? Didn't it take longer than if you'd just written the views by hand? Yes, probably did, but not by much. And I didn't have to spend much time testing the output view, since it was generated directly from the data that defines the view as being correct. Plus what happens if the number of steps change, do I want to change the pivot view by hand each time? For example, we could actually modify the stored proc to put the code into a variable which it then executes, rather than outputting. Once that is in place, we can create a scheduled task to automatically run this stored procedure on a scheduled basis to ensure that the pivot view is always correct.
Technorati Tags: SQL Server,SCCM,Tips