CODEWITHSUNDEEP
sqlsql-server
James Khan
James Khan

Reputation: 841

Flip the data in sql

I need to get this view for a report.

Transaction Type    Amount (£) Credit   Product 1 (£)   Product 2 (£)   Product 3 (£)
Credit Card         2489.58             829.86          739.02          920.70
Debit Card          2314.93             1157.47         987.34          170.13
Cheques / P Orders  11266.38            3755.46         4068.92         3442.00
Credit Card - Web   1974.21             984.93          474.21          515.07
GRAND TOTAL         18045.10            6727.72         6269.49         5047.90

Currently I have this , nearly there ...

Transaction Type    AMOUNT (£) CREDIT   Product Type
Bank Transfer       653.89              Product type 1
Card                1323.24             Product type 1
Cash                4538.12             Product type 1
Direct Debit        118.80              Product type 1
Bank Transfer       653.89              Product type 2
Card                1323.24             Product type 2
Cash                4538.12             Product type 2
Direct Debit        118.80              Product type 2

What do I need to change in my query ?

SELECT PT.description          AS [Transaction Type], 
       Sum (P.original_amount) AS [AMOUNT (£) CREDIT], 
       PR.[description]        AS [Product Type] 
FROM   [X].[dbo].[payment] P 
       JOIN [X].[dbo].[table1] PT 
         ON P.payment_id = PT.payment_type_id 
       JOIN [X].[dbo].[table2] SO 
         ON SO.payment_type_id = P.payment_type_id 
       JOIN [X].[dbo].[table3] OI 
         ON OI.order_id = SO.site_order_id 
       JOIN [X].[dbo].[table4] PR 
         ON PR.product_id = OI.product_id 
GROUP  BY PT.description, 
          PR.description

Upvotes: 0

Views: 112

Answers (1)

Lamak
Lamak

Reputation: 70658

This should do:

SELECT PT.[DESCRIPTION] as [Transaction Type],
       SUM(P.ORIGINAL_AMOUNT) as [AMOUNT (£) CREDIT],
       SUM( CASE WHEN PR.[Description] = 'Product type 1' 
            THEN P.ORIGINAL_AMOUNT END) [Product 1 (£)],
       SUM( CASE WHEN PR.[Description] = 'Product type 2' 
            THEN P.ORIGINAL_AMOUNT END) [Product 2 (£)],
       SUM( CASE WHEN PR.[Description] = 'Product type 3' 
            THEN P.ORIGINAL_AMOUNT END) [Product 3 (£)]
FROM [X].[dbo].[PAYMENT] P
join [X].[dbo].[TABLE1] PT on P.PAYMENT_ID=PT.PAYMENT_TYPE_ID
join [X].[dbo].[TABLE2] SO on SO.PAYMENT_TYPE_ID=P.PAYMENT_TYPE_ID
join [X].[dbo].[TABLE3] OI on OI.ORDER_ID=SO.SITE_ORDER_ID
join [X].[dbo].[TABLE4] PR on PR.Product_id=OI.PRODUCT_ID
GROUP BY PT.[DESCRIPTION]

UPDATED Well, since you need a dynamic number of columns, you will need dynamic SQL (as njk said). I'll post one way to do this with PIVOT, though it need SQL Server 2005+.

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

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

SET @Query = '
;WITH CTE AS(
    SELECT PT.[description]        AS [Transaction Type], 
           Sum (P.original_amount) AS [AMOUNT (£) CREDIT], 
           PR.[description]        AS [Product Type] 
    FROM   [X].[dbo].[payment] P 
           JOIN [X].[dbo].[table1] PT 
             ON P.payment_id = PT.payment_type_id 
           JOIN [X].[dbo].[table2] SO 
             ON SO.payment_type_id = P.payment_type_id 
           JOIN [X].[dbo].[table3] OI 
             ON OI.order_id = SO.site_order_id 
           JOIN [X].[dbo].[table4] 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(@Query)

Upvotes: 2

Related Questions