CODEWITHSUNDEEP
sqlsql-server-2008
Goolsy
Goolsy

Reputation: 237

Second Pivot within SQL query

I have a query already pivoting data relating to transaction data. I have a table that contains customer numbers, transaction type and transaction amount.

My original query was returning the total number of transactions each customer was performing broken down by transaction type. It is below:

;WITH query AS
(
SELECT customernum,[Cash],[Card]
FROM (
transactions
)
AS source
PIVOT
(
COUNT(TransType) FOR TransType IN ([Cash],[Card])
)
AS pvt
) SELECT customernum AS 'Customer Number', SUM([Cash]) AS 'Cash Transactions', 
SUM([Card]) AS 'Card Transactions' FROM query
GROUP BY customernum
ORDER BY customernum

I now need to add the total value to the query but still broken down by transaction type.

By example, the sample data from table 'transactions' is:

╔═════════════╦═══════════╦════════╗
║ CustomerNum ║ TransType ║ Amount ║
╠═════════════╬═══════════╬════════╣
║         111 ║ Cash      ║      5 ║
║         111 ║ Card      ║      5 ║
║         111 ║ Cash      ║      5 ║
║         111 ║ Cash      ║      5 ║
║         111 ║ Card      ║      5 ║
║         222 ║ Card      ║      5 ║
║         222 ║ Cash      ║      5 ║
║         222 ║ Card      ║      5 ║
║         333 ║ Cash      ║      5 ║
║         333 ║ Cash      ║      5 ║
║         333 ║ Cash      ║      5 ║
╚═════════════╩═══════════╩════════╝

My original query was producing this result:

╔═════════════╦══════╦══════╗
║ CustomerNum ║ Cash ║ Card ║
╠═════════════╬══════╬══════╣
║         111 ║    3 ║    2 ║
║         222 ║    1 ║    2 ║
║         333 ║    3 ║    0 ║
╚═════════════╩══════╩══════╝

I now need it to return this:

╔═════════════╦══════╦═════════════╦══════╦═════════════╗
║ CustomerNum ║ Cash ║ Cash Amount ║ Card ║ Card Amount ║
╠═════════════╬══════╬═════════════╬══════╬═════════════╣
║         111 ║    3 ║          15 ║    2 ║          10 ║
║         222 ║    1 ║           5 ║    2 ║          10 ║
║         333 ║    3 ║          15 ║    0 ║           0 ║
╚═════════════╩══════╩═════════════╩══════╩═════════════╝

Upvotes: 0

Views: 47

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Just use conditional aggregation:

SELECT CustomerNum,
       SUM(CASE WHEN TransType = 'Cash' THEN 1 ELSE 0 END) AS cash,
       SUM(CASE WHEN TransType = 'Cash' THEN amount ELSE 0 END) AS cash_amount,
       SUM(CASE WHEN TransType = 'Card' THEN 1 ELSE 0 END) AS card,
       SUM(CASE WHEN TransType = 'Card' THEN amount ELSE 0 END) AS card_amount
FROM transactions
GROUP BY CustomerNum;

Upvotes: 2

Parfait
Parfait

Reputation: 107687

Consider using two pivot queries as derived tables:

SELECT t1.customernum, t1.Cash, t2.Cash As [Cash Amount],
       t1.Card, t2.Card As [Card Amount]

FROM

    (SELECT * FROM
         (SELECT customernum, TransType
          FROM transactions) AS src1
     PIVOT
     (
      COUNT(TransType) FOR TransType IN ([Cash],[Card])
     )
     AS pvt1) As t1

INNER JOIN

   (SELECT * FROM
        (SELECT customernum, TransType, Amount
         FROM transactions) AS src2
    PIVOT
    (
     SUM(Amount) FOR TransType IN ([Cash],[Card])
    )
    AS pvt2) As t2

ON t1.customernum = t2.customernum

SQLFiddle

Upvotes: 0

Related Questions