CODEWITHSUNDEEP
sqlsql-serverpivot
James Khan
James Khan

Reputation: 841

Pivot sql with totals

Now I have a query to get me this ;

Transaction Type    Product 1 (£)   Product 2 (£)   Product 3 (£)


Credit Card 1   739.02  920.70
Debit Card  3   987.34  170.13
Cheques / P Orders  7   4068.92 3442.00
Credit Card - Web   1   474.21  515.07

But now I need this last line with totals. 

GRAND TOTAL 12  6269.49 5047.90

Cant see to use rollups with my pivot sql ? Any ideas what I could use to get that last line ?

DECLARE @ProductTypes AS NVARCHAR(MAX), @Query1  AS NVARCHAR(MAX);

SELECT @ProductTypes = STUFF((SELECT DISTINCT ',' + QUOTENAME([Description]) 
                              FROM [X].[dbo].[PRODUCT] 
                              FOR XML PATH(''), 
                              TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')


SET @Query1 = '

;WITH CTE AS
(

    SELECT PT.[description]       AS [Transaction Type], 
           Sum (P.original_amount) AS [AMOUNT (£) CREDIT], 
           PR.[description]        AS [Product Type] 
    FROM    [X].[dbo].[Table1] P
join [X].[dbo].[Table2] PT on P.PAYMENT_ID=PT.PAYMENT_TYPE_ID
join [X].[dbo].[Table3] SO on SO.PAYMENT_TYPE_ID=P.PAYMENT_TYPE_ID
join [X].[dbo].[Table4] OI on OI.ORDER_ID=SO.SITE_ORDER_ID
join [C].[dbo].Table5] PR on PR.Product_id=OI.PRODUCT_ID
group by PT.DESCRIPTION, PR.DESCRIPTION
)
SELECT *
FROM CTE AS T
PIVOT(SUM([AMOUNT (£) CREDIT]) FOR [Product Type] IN ('+@ProductTypes+')) AS PT
'


EXEC(@Query1)

Upvotes: 1

Views: 419

Answers (2)

Mani Pav
Mani Pav

Reputation: 1

You can dynamically pivot for all the values in a pivot column

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

select @cols = (SELECT string_agg ( QUOTENAME(PivotColumnName) ,',')

                   From   
                    (
                      select distinct(PivotColumnName)
                      from yourtable
                    )
                        )

 set @query = N'SELECT * from 
         (
            select value, PivotColumnName
            from yourtable
        ) x
        pivot 
        (
            max(value)
            for PivotColumnName in (' + @cols + N')
        ) p '

            exec sp_executesql @query;

Upvotes: 0

Taryn
Taryn

Reputation: 247690

You can use ROLLUP to get the result that you want, you will just have to alter your code slight to do it.

Your code will be similar to this:

DECLARE @ProductTypes AS NVARCHAR(MAX), 
    @ProductTypesSum AS NVARCHAR(MAX),
    @Query1  AS NVARCHAR(MAX);

SELECT @ProductTypes = STUFF((SELECT DISTINCT ',' + QUOTENAME([Description]) 
                              FROM [X].[dbo].[PRODUCT] 
                              FOR XML PATH(''), 
                              TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

SELECT @ProductTypesSum = STUFF((SELECT DISTINCT ', Sum(' + QUOTENAME([Description])+') as '+QUOTENAME([Description])
                              FROM [X].[dbo].[PRODUCT] 
                              FOR XML PATH(''), 
                              TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')


SET @Query1 = '

;WITH CTE AS
(
    SELECT PT.[description]       AS [Transaction Type], 
           Sum (P.original_amount) AS [AMOUNT (£) CREDIT], 
           PR.[description]        AS [Product Type] 
    FROM    [X].[dbo].[Table1] P
    join [X].[dbo].[Table2] PT on P.PAYMENT_ID=PT.PAYMENT_TYPE_ID
    join [X].[dbo].[Table3] SO on SO.PAYMENT_TYPE_ID=P.PAYMENT_TYPE_ID
    join [X].[dbo].[Table4] OI on OI.ORDER_ID=SO.SITE_ORDER_ID
    join [C].[dbo].Table5] PR on PR.Product_id=OI.PRODUCT_ID
    group by PT.DESCRIPTION, PR.DESCRIPTION
)
SELECT 
    case 
        when [Transaction Type] is not null 
        then [Transaction Type] 
        else ''Grand Total'' end as [Transaction Type],
    '+@ProductTypesSum+'
FROM CTE AS T
PIVOT
(
    SUM([AMOUNT (£) CREDIT]) 
    FOR [Product Type] IN ('+@ProductTypes+')
) AS PT
group by [Transaction Type] with rollup'


EXEC(@Query1)

See SQL Fiddle with Demo with modified code.

Upvotes: 4

Related Questions