PIVOTS with Unknown Values – Dynamic SQL

PIVOTS with Unknown Values – Dynamic SQL

Comments 1 comment

As promised, although it took 3 years, I’ll show you how to use PIVOTS with unknown values. This is a continuation of the other PIVOT post. If you haven’t already, it’s not a bad idea to read it first. (Create Employee Work Shift Schedule using PIVOTS)

We’ll use the same code to start off. The prep code is the same.

CREATE TABLE #DATA (
      name VARCHAR(50),
      shift_start DATETIME,
      shift_end DATETIME
)
INSERT INTO #DATA VALUES ('Boony Antonchik', '2017-05-30 14:00', '2017-05-30 17:00')
INSERT INTO #DATA VALUES ('Toma Wittier', '2017-05-30 14:00', '2017-05-30 15:00')
INSERT INTO #DATA VALUES ('Genvieve Arends', '2017-05-30 8:00', '2017-05-30 13:00')
INSERT INTO #DATA VALUES ('Ulla Friatt', '2017-05-30 18:00', '2017-05-30 19:00')
INSERT INTO #DATA VALUES ('Giralda Ranfield', '2017-05-30 13:00', '2017-05-30 15:00')
INSERT INTO #DATA VALUES ('Alvis Duester', '2017-05-30 13:00', '2017-05-30 20:00')
INSERT INTO #DATA VALUES ('Rhianna Sherar', '2017-05-30 13:00', '2017-05-30 16:00')
INSERT INTO #DATA VALUES ('Liane Bayldon', '2017-05-30 12:00', '2017-05-30 19:00')
INSERT INTO #DATA VALUES ('Cookie Saffle', '2017-05-30 9:00', '2017-05-30 12:00')
INSERT INTO #DATA VALUES ('Rosanne Paffley', '2017-05-30 13:00', '2017-05-30 19:00')

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

;WITH cte (datelist, maxdate) AS
(
    SELECT min(shift_start) datelist, max(shift_end) maxdate
    FROM #DATA
    UNION all
    SELECT dateadd(HOUR, 1, datelist), maxdate
    FROM cte
    WHERE datelist < maxdate
)
SELECT c.datelist
INTO #TEMPDATES
FROM cte c

Last time we predefined the time values like the code below

SELECT name, [08:00], [09:00], [10:00], [11:00], [12:00], [13:00] , [14:00], [15:00], [16:00], [17:00], [18:00], [19:00], [20:00]
INTO #PIVOTED
FROM
(
    SELECT name, shift_start, shift_end, time_list,
        CONVERT(TIME, time_list) PivotDate
    FROM #TEMPDATES t
    LEFT JOIN #DATA d
        ON t.time_list between shift_start and shift_end
) x
PIVOT 
(
    COUNT(time_list)
    FOR PivotDate IN ([08:00], [09:00], [10:00], [11:00], [12:00], [13:00] , [14:00], [15:00], [16:00], [17:00], [18:00], [19:00], [20:00])
) p

But instead of listing the times, let’s concatenate the times into a NVARCHAR(MAX) parameter

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(CONVERT(TIME, datelist))
                    from #TEMPDATES
            FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)')
        ,1,1,'')

The parameter @cols now has a string of the times comma separated and ready to be used columns for our pivot

[08:00:00.0000000],[09:00:00.0000000],[10:00:00.0000000],[11:00:00.0000000],[12:00:00.0000000],[13:00:00.0000000],[14:00:00.0000000],[15:00:00.0000000],[16:00:00.0000000],[17:00:00.0000000],[18:00:00.0000000],[19:00:00.0000000],[20:00:00.0000000]

And now for the PIVOT. The PIVOT statement will be the same except we will replace the list of times with the @cols parameter.

Also, we will use dynamic SQL.
We’ll first write the PIVOT, then use the PIVOT to populate the <tr><td> sets for the data.

set @query = 'SELECT name, ' + @cols + '
		into #PIVOTED from (
			select s.name, s.shift_start, s.shift_end, d.datelist, CONVERT(TIME, datelist) PivotDate
			from #TEMPDATES d
			left join #DATA s on d.datelist between s.shift_start and s.shift_end
		) x
		pivot (
			count(datelist)
			for PivotDate in (' + @cols + ')
		) p
			
	SELECT @result = CAST((
		SELECT name td, ' + REPLACE(@cols, ',', ' td,') + ' td
		FROM    #PIVOTED
		FOR XML RAW(''tr''), ELEMENTS
	) AS NVARCHAR(MAX))'

Now we can use sp_executesql to execute the dynamic sql.
We’ll output the html formatted result into the @PIVOTHTML parameter

EXECUTE sp_executesql @query, N'@result NVARCHAR(MAX) OUTPUT', @result=@PIVOTHTML OUTPUT

With the html formatted data, we can build the html table

DECLARE @body VARCHAR(MAX)
SET     @body = '<html><head>'
	+ '<style type="text/css">'
	+ '#sample table {font-size: 0.6em;}'
	+ '#sample .one {background-color: #32cd32;}'
	+ '</style>'
	+ '</head><body>'
	+ '<div id=''sample''><table>'
    + '<tr><th>Name</th>' + REPLACE(REPLACE(REPLACE(@cols, ':00.0000000]', '</th>'), '[', '<th>'), ',', '') + '</tr>'
    + @PIVOTHTML
    + '</table></div></body></html>'
    
SET @body = REPLACE(@body, '<td>1</td>', '<td class=''one'' />')
SET @body = REPLACE(@body, '<td>0</td>', '<td />')

SELECT @body

That’s it!
We used CTE (common table expression), PIVOT and Dynamic SQL
The complete code is below. 

CREATE TABLE #DATA (
      name VARCHAR(50),
      shift_start DATETIME,
      shift_end DATETIME
)
INSERT INTO #DATA VALUES ('Boony Antonchik', '2017-05-30 14:00', '2017-05-30 17:00')
INSERT INTO #DATA VALUES ('Toma Wittier', '2017-05-30 14:00', '2017-05-30 15:00')
INSERT INTO #DATA VALUES ('Genvieve Arends', '2017-05-30 8:00', '2017-05-30 13:00')
INSERT INTO #DATA VALUES ('Ulla Friatt', '2017-05-30 18:00', '2017-05-30 19:00')
INSERT INTO #DATA VALUES ('Giralda Ranfield', '2017-05-30 13:00', '2017-05-30 15:00')
INSERT INTO #DATA VALUES ('Alvis Duester', '2017-05-30 13:00', '2017-05-30 20:00')
INSERT INTO #DATA VALUES ('Rhianna Sherar', '2017-05-30 13:00', '2017-05-30 16:00')
INSERT INTO #DATA VALUES ('Liane Bayldon', '2017-05-30 12:00', '2017-05-30 19:00')
INSERT INTO #DATA VALUES ('Cookie Saffle', '2017-05-30 9:00', '2017-05-30 12:00')
INSERT INTO #DATA VALUES ('Rosanne Paffley', '2017-05-30 13:00', '2017-05-30 19:00')

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

;WITH cte (datelist, maxdate) AS
(
    SELECT min(shift_start) datelist, max(shift_end) maxdate
    FROM #DATA
    UNION all
    SELECT dateadd(HOUR, 1, datelist), maxdate
    FROM cte
    WHERE datelist < maxdate
)
SELECT c.datelist
INTO #TEMPDATES
FROM cte c

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(CONVERT(TIME, datelist))
                    from #TEMPDATES
            FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)')
        ,1,1,'')
		
set @query = 'SELECT name, ' + @cols + '
		into #PIVOTED from (
			select s.name, s.shift_start, s.shift_end, d.datelist, CONVERT(TIME, datelist) PivotDate
			from #TEMPDATES d
			left join #DATA s on d.datelist between s.shift_start and s.shift_end
		) x
		pivot (
			count(datelist)
			for PivotDate in (' + @cols + ')
		) p
			
	SELECT @result = CAST((
		SELECT name td, ' + REPLACE(@cols, ',', ' td,') + ' td
		FROM    #PIVOTED
		FOR XML RAW(''tr''), ELEMENTS
	) AS NVARCHAR(MAX))'
			
EXECUTE sp_executesql @query, N'@result NVARCHAR(MAX) OUTPUT', @result=@PIVOTHTML OUTPUT

DECLARE @body VARCHAR(MAX)
SET     @body = '<html><head>'
	+ '<style type="text/css">'
	+ '#sample table {font-size: 0.6em;}'
	+ '#sample .one {background-color: #32cd32;}'
	+ '</style>'
	+ '</head><body>'
	+ '<div id=''sample''><table>'
    + '<tr><th>Name</th>' + REPLACE(REPLACE(REPLACE(@cols, ':00.0000000]', '</th>'), '[', '<th>'), ',', '') + '</tr>'
    + @PIVOTHTML
    + '</table></div></body></html>'
    
SET @body = REPLACE(@body, '<td>1</td>', '<td class=''one'' />')
SET @body = REPLACE(@body, '<td>0</td>', '<td />')

SELECT @body

DROP TABLE #TEMPDATES
DROP TABLE #DATA
https://gist.github.com/jon-kim/7fd7be8242704bd0d9d9e10a054ba4e0

One thought on “PIVOTS with Unknown Values – Dynamic SQL”

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.