[Solved] How do I pivot with hour format(HH:HourNumber)?

by


First of all create a temp table to use it in 3 places – Select columns for pivot, Replace null with zero and inside pivot.

SELECT DISTINCT
SUM(ORDERTOTAL) OVER(PARTITION BY CAST(ORDERTIME AS DATE),DATEPART(HH,ORDERTIME)) [TOTAL],
CONVERT(varchar, CAST(ORDERTIME AS datetime), 103) [DATE],
DATEPART(HH,ORDERTIME) [HOUR],
'HH:'+CAST(DATEPART(HH,ORDERTIME) AS VARCHAR(3)) [HOURCOL]
INTO #NEWTABLE
FROM ORDERTBL
ORDER BY DATEPART(HH,ORDERTIME)

Now declare 2 variables to select columns for pivot and replace null with zero

DECLARE @cols NVARCHAR (MAX)
DECLARE @NullToZeroCols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + ',[' + [HOURCOL] + ']', 
              '[' + [HOURCOL] + ']')
               FROM    (SELECT DISTINCT [HOUR],[HOURCOL] FROM #NEWTABLE) PV  
               ORDER BY [HOUR]

SET @NullToZeroCols = SUBSTRING((SELECT ',ISNULL(['+[HOURCOL]+'],0) AS ['+[HOURCOL]+']' 
FROM(SELECT DISTINCT [HOUR],[HOURCOL] FROM #NEWTABLE GROUP BY [HOUR],[HOURCOL])TAB  
ORDER BY [HOUR]  FOR XML PATH('')),2,8000)

Now pivot the result

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT [DATE],' + @NullToZeroCols + ' FROM 
             (
                 SELECT [HOURCOL],[TOTAL], [DATE] FROM #NEWTABLE
             ) x
             PIVOT 
             (
                 SUM([TOTAL])
                 FOR [HOURCOL] IN (' + @cols + ')
            ) p
            ;' 

EXEC SP_EXECUTESQL @query

4

solved How do I pivot with hour format(HH:HourNumber)?