Skip to content

Instantly share code, notes, and snippets.

@jon-kim

jon-kim/CreateEmployeeTimetable_Pivot_Dynamic.SQL

Last active May 30, 2020 02:57
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save jon-kim/7fd7be8242704bd0d9d9e10a054ba4e0 to your computer and use it in GitHub Desktop.
Save jon-kim/7fd7be8242704bd0d9d9e10a054ba4e0 to your computer and use it in GitHub Desktop.
Download ZIP
Pivot with Dynamic column values
Raw
CreateEmployeeTimetable_Pivot_Dynamic.SQL
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment