SNOWFLAKE - PIVOT QUERY

Question

Table 1:

ID AUDIT_KEY Col_Name
1  100        FULL NAME
2  101        FNAME
3  102        LNAME
4  103        ADDR1

Table 2:

ID_key  AUDITKEY  Col_Value
1       100       john abraham
2       101       johny
3       102       Abraham
4       103       6900 Forester Rd

1  104       Praveen Gupta
2  105       Praveen
3  106       Gupta
4  107       3200 Walter RD

Looking for QUERY TO FORM THE RESULT AS BELOW

ID NAME           FNAME    LNAME     ADDR1
1  JOHN ABRAHAM   JOHNY    ABRAHAM   6900 Forester Rd
2  PRAVEEN GUPTA  PRAVEEN  GUPTA     3200 WALTER RD

Written pivot query on table1 when i join table2 based on the ID & ID_KEY is not working.. any idea folks..

SELECT ID, NAME,FNAME,LNAME,ADDR1 
FROM TABLE1 
INNER JOIN TABLE2 ON TABLE1.ID = TABLE2.ID 
PIVOT (MAX(TABLE1.COL_NAME) 
       FOR TABLE2.COL_VALUE IN ('ID','NAME','FNAME','LNAME','ADDR1')) AS TMP

Answer 1

I would suggest doing the join in a CTE and then do the pivot against the product of that. something along the lines of:

WITH x AS (
  SELECT t1.col_name, t2.col_value
  FROM TABLE1 t1
  INNER JOIN TABLE2 t2
  ON t1.id = t2.id
  ) 
SELECT *
PIVOT (MAX(COL_NAME) 
       FOR COL_VALUE IN ('NAME','FNAME','LNAME','ADDR1')) AS TMP;

Note - I haven't tested this myself, so see if it works. I don't think Snowflake likes join and pivot in the same query, so doing the join inside a CTE provides a single table input into the pivot function.

Also, note, you had 'ID' in the IN clause, but that isn't a col_value value in your sample data, so you don't want it there. That could also be your issue.