Tivot Tivot - shared row and dynamic columns
Let him jump right into it. Here's the code
SELECT [prov], [201304], [201305], [201306], [201307]
FROM (
SELECT [prov], [arrival], [Amount]
FROM [tblSource]) up
PIVOT (SUM([Amount]) FOR [arrival] IN ([201304], [201305], [201306], [201307])) AS pvt
GO
This brings me back to that lovely table. I was wondering how I would get the totals for each "date" column to appear in the last row added?
In addition, more data will be added to the base table, in particular more dates. This means that after that 201308 will be added , then 201309 , etc.
This would mean that I currently have to change the code above each month to reflect the addition. Anyway, around?
source to share
You can create columns dynamically using dynamic SQL, however I would recommend dealing with dynamic centers in a layer created for it like SSRS or excel.
DECLARE @SQL NVARCHAR(MAX) = '',
@SQL2 NVARCHAR(MAX) = '',
@SQL3 NVARCHAR(MAX) = '';
-- COMPILE THE UNIQUE VALUES FOR ARRIVAL THAT NEED TO BE PIVOTED
SELECT @SQL = @SQL + ',' + QUOTENAME(Arrival),
@SQL2 = @SQL2 + '+ISNULL(' + QUOTENAME(Arrival) + ', 0)',
@SQL3 = @SQL3 + ',' + QUOTENAME(Arrival) + ' = ISNULL(' + QUOTENAME(Arrival) + ', 0)'
FROM (SELECT DISTINCT Arrival FROM tblSource) s;
-- COMBINE THEM INTO A SINGLE QUERY
SET @SQL = 'SELECT [Prov]' + @SQL3 + ', [Total] = ' + STUFF(@SQL2, 1, 1, '') + '
FROM ( SELECT Arrival, Prov, Amount
FROM [tblSource]
UNION ALL
SELECT Arrival, ''Total'', SUM(Amount)
FROM [tblSource]
GROUP BY Arrival
) up
PIVOT
( SUM(Amount)
FOR Arrival IN (' + STUFF(@SQL, 1, 1, '') + ')
) pvt;';
-- EXECUTE THE QUERY
EXECUTE SP_EXECUTESQL @SQL;
This creates and executes the following SQL:
SELECT [Prov],
[2013-01-01] = ISNULL([2013-01-01], 0),
[2013-02-01] = ISNULL([2013-02-01], 0),
[Total] = ISNULL([2013-01-01], 0) + ISNULL([2013-02-01], 0)
FROM ( SELECT Arrival, Prov, Amount
FROM [tblSource]
UNION ALL
SELECT Arrival, 'Total', SUM(Amount)
FROM [tblSource]
GROUP BY Arrival
) up
PIVOT
( SUM(Amount)
FOR Arrival IN ([2013-01-01],[2013-02-01])
) pvt;
This is the query below the join in the subquery up
that adds the total row at the bottom and the grand total is simply generated by adding all the columns in the row.
I will stress again that I do recommend manipulating data like this outside of SQL.
EDIT
An alternative to using UNION to get a generic string is to use GROUPING SETS
like this:
DECLARE @SQL NVARCHAR(MAX) = '',
@SQL2 NVARCHAR(MAX) = '',
@SQL3 NVARCHAR(MAX) = '';
-- COMPILE THE UNIQUE VALUES FOR ARRIVAL THAT NEED TO BE PIVOTED
SELECT @SQL = @SQL + ',' + QUOTENAME(Arrival),
@SQL2 = @SQL2 + '+ISNULL(' + QUOTENAME(Arrival) + ', 0)',
@SQL3 = @SQL3 + ',' + QUOTENAME(Arrival) + ' = ISNULL(' + QUOTENAME(Arrival) + ', 0)'
FROM (SELECT DISTINCT Arrival FROM tblSource) s;
-- COMBINE THEM INTO A SINGLE QUERY
SET @SQL = 'SELECT [Prov]' + @SQL3 + ', [Total] = ' + STUFF(@SQL2, 1, 1, '') + '
FROM ( SELECT Arrival, Prov = ISNULL(Prov, 'Total'), Amount = SUM(Amount)
FROM [tblSource]
GROUP BY GROUPING SETS((Prov, arrival), (arrival))
) up
PIVOT
( SUM(Amount)
FOR Arrival IN (' + STUFF(@SQL, 1, 1, '') + ')
) pvt;';
-- EXECUTE THE QUERY
EXECUTE SP_EXECUTESQL @SQL;
source to share
SAMPLE TABLE
CREATE TABLE #TEMP([prov] VARCHAR(100),[arrival] INT, AMOUNT NUMERIC(12,2))
INSERT INTO #TEMP
SELECT 'A' [prov],'201304' [arrival],100 AMOUNT
UNION ALL
SELECT 'A' ,'201305' ,124
UNION ALL
SELECT 'A' ,'201306' ,156
UNION ALL
SELECT 'B' ,'201304' ,67
UNION ALL
SELECT 'B' ,'201305' ,211
UNION ALL
SELECT 'B' ,'201306' ,176
UNION ALL
SELECT 'C' ,'201304' ,43
UNION ALL
SELECT 'C' ,'201305' ,56
UNION ALL
SELECT 'C' ,'201306' ,158
QUERY
You can use ROLLUP to get the grand total. More about ROLLUP here
-- Get the columns for dynamic pivot
DECLARE @cols NVARCHAR (MAX)
SELECT @cols = COALESCE (@cols + ',[' + CAST([arrival] AS VARCHAR(50)) + ']',
'[' + CAST([arrival] AS VARCHAR(50)) + ']')
FROM (SELECT DISTINCT [arrival] FROM #TEMP) PV
ORDER BY [arrival]
-- Replace NULL value with zero
DECLARE @NulltoZeroCols NVARCHAR (MAX)
SELECT @NullToZeroCols = SUBSTRING((SELECT ',ISNULL(['+[arrival]+'],0) AS ['+[arrival]+']'
FROM (SELECT DISTINCT CAST([arrival] AS VARCHAR(50)) [arrival] FROM #TEMP)TAB
ORDER BY CAST([arrival]AS INT) FOR XML PATH('')),2,8000)
DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT [prov],' + @NullToZeroCols + ' FROM
(
SELECT
ISNULL([prov],''Total'')[prov],
SUM(AMOUNT)AMOUNT ,
ISNULL(CAST([arrival] AS VARCHAR(50)),''Total'')[arrival]
FROM #TEMP
GROUP BY [arrival],[prov]
WITH ROLLUP
) x
PIVOT
(
MIN(AMOUNT)
FOR [arrival] IN (' + @cols + ')
) p
ORDER BY CASE WHEN ([prov]=''Total'') THEN 1 ELSE 0 END,[prov]'
EXEC SP_EXECUTESQL @query
Note. If you don't want to replace NULL
with zero
, just replace @NullToZeroCols
with @cols
in the outer query of the dynamic pivot
source to share