CODEWITHSUNDEEP
snowflake-cloud-data-platform
jay
jay

Reputation: 1463

Transposing with column concatenation in snowflake

I have a sample data as follows;

ID   | TXN | Bank | Cheque
-----------------------
70046| USD | 10   |  0
70046| FX  | 70   |  2
70194| Dom | 0    |  6

The expected output shd be as follows;

Expected Output

ID   | USD_Bank | USD_Cheque | FX_Bank | FX_Cheque | Dom_Bank  | Dom_Cheque
-----|----------|------------|---------|-----------|-----------|----------
70046|   10     |   0        |  70     |    2      |  0        |    0
70194|   0      |   0        |  0      |    0      |   0       |    6

Here is my attempted code;

 SELECT *
 FROM table
pivot(MAX(Bank) FOR TXN IN  ('FX', 'USD', 'Dom')),
pivot(MAX(Cheque) FOR TXN IN  ('FX', 'USD', 'Dom'))
AS P 
ORDER BY ID

But this gives an error; SQL compilation error: syntax error line 82 at position 9 unexpected '('.

Can I get some help on how to get expected output?thanks

Upvotes: 1

Views: 230

Answers (2)

JNevill
JNevill

Reputation: 50034

I would suggest doing this old-school SQL and using CASE expressions:

SELECT id, 
    CASE WHEN TXN = 'USD' THEN Bank END as Bank_USD,
    CASE WHEN TXN = 'FX' THEN Bank END AS Bank_FX,
    CASE WHEN TXN = 'Dom' THEN Bank END AS Bank_Dom,
    CASE WHEN TXN = 'USD' THEN Cheque END as Cheque_USD,
    CASE WHEN TXN = 'FX' THEN Cheque END AS Cheque_FX,
    CASE WHEN TXN = 'Dom' THEN Cheque END AS Cheque_Dom
From yourtable;

Adding/removing different TXN and Bank/Cheque values in this is going to be much easier than monkeying with multiple lists, aliases, and references in the SELECT clause as you'll be forced to do with a PIVOT clause.

Because Snowflake can't pivot on more than one attribute column, if you have to do this with a pivot it's going to get ugly since two pivots and a join are necessary to get your data together:

SELECT bankpivot.ID, Bank_FX, Bank_USD, Bank_Dom
    ,cheque_FX, cheque_USD, cheque_Dom
FROM
    (
      SELECT *
      FROM yourtable
      PIVOT(MAX(Bank) FOR TXN IN  ('FX', 'USD', 'Dom')) as P
    ) bankpivot (ID, Cheque, Bank_FX, Bank_USD, Bank_Dom)
    INNER JOIN
    (
      SELECT *
      FROM yourtable
      PIVOT(MAX(cheque) FOR TXN IN  ('FX', 'USD', 'Dom')) as P
    ) chequepivot (ID, Bank, Cheque_FX, Cheque_USD, Cheque_Dom)
    ON bankpivot.id = chequepivot.id

Upvotes: 0

Maja F.
Maja F.

Reputation: 333

I suggest you do each pivot separately and then combine the results with an inner join, something along the lines of:

with CTE as (
select 70046 as ID, 'USD' as TXN, 10 as Bank, 0 as Cheque union all
select 70046 as ID, 'FX'  as TXN, 70 as Bank, 2 as Cheque union all
select 70194 as ID, 'Dom' as TXN, 0  as Bank, 6 as Cheque
),
Bank_pivot as (
select ID, "'USD'" as USD_Bank, "'FX'" as FX_Bank, "'Dom'" as Dom_Bank 
from (select ID, TXN, Bank from CTE)
pivot(
      max(Bank)
      for TXN in ('USD', 'FX', 'Dom')
     )
),
Cheque_pivot as (
select ID, "'USD'" as USD_Cheque, "'FX'" as FX_Cheque, "'Dom'" as Dom_Cheque 
from (select ID, TXN, Cheque from CTE)
pivot(
      max(Cheque)
      for TXN in ('USD', 'FX', 'Dom')
     )
)
select B.ID, B.USD_Bank, C.USD_Cheque, B.FX_Bank, C.FX_Cheque, B.Dom_Bank, C.Dom_Cheque
from Bank_pivot B
inner join Cheque_pivot C
on B.ID = C.ID
;

Upvotes: 1

Related Questions