CODEWITHSUNDEEP
snowflake-cloud-data-platformtransposeunpivot
Angie
Angie

Reputation: 327

How to transpose a table in snowflake

I have a table like

ID NAME CURRENT_DATE SOURCE
5 NULL 2023-01-01 A
5 JESSICA 2023-02-01 B

that I want to transpose the table so that the result would look like

FIELD A B
ID 5 5
NAME NULL JESSICA
DATE 2023-01-01 2023-02-01

where I can compare the fields based on the SOURCE column. Is there a way I can do this? Thanks!

Upvotes: 0

Views: 8892

Answers (2)

Alex Mirkin
Alex Mirkin

Reputation: 21

If you only have two sources, and various number of columns to compare - you may consider this solution:

-- Create your_table
CREATE OR REPLACE TEMP TABLE your_table AS
SELECT $1 AS id
     , $2 AS name
     , $3 AS event_date
     , $4 AS source
  FROM
VALUES (5, NULL, '2023-01-01'::date, 'A')
     , (5, 'JESSICA', '2023-02-01'::date, 'B');

-- Compare source A to B
  WITH base AS
           (SELECT source
                 , columns.key AS field
                 , columns.value
              FROM (SELECT source
                         , OBJECT_DELETE(OBJECT_CONSTRUCT_KEEP_NULL(*), 'SOURCE') AS obj
                      FROM your_table)
                 , LATERAL FLATTEN(INPUT => obj) AS columns
           )
SELECT t1.field
     , t1.value AS a
     , t2.value AS b
  FROM base AS t1
  JOIN base AS t2
       ON t1.field = t2.field
           AND t1.source = 'A'
           AND t2.source = 'B';

Upvotes: 0

Tushar
Tushar

Reputation: 3623

If you have CURRENT_DATE as a column name which is a snowflake reserved keyword; you need to escape it using double quotes while using as a column_name in query

The following query using PIVOT and UNPIVOT in snowflake shall give you expected output.

SELECT *
FROM (
  SELECT FIELD, SOURCE, VALUE
  FROM YOUR_TABLE
  UNPIVOT (VALUE FOR FIELD IN (ID, NAME, "CURRENT_DATE"))
)
PIVOT (
  MAX(VALUE) FOR SOURCE IN ('A', 'B')
);

Output :

FIELD A B
ID 5 5
NAME NULL JESSICA
DATE 2023-01-01 2023-02-01

Upvotes: 2

Related Questions