CODEWITHSUNDEEP
sqlunpivotsnowflake-schema
HuLu ViCa
HuLu ViCa

Reputation: 5450

Unpivot snowflake table with pivot column alias

I got the following example of how to unpivot Snowflake tables:

SELECT *
  FROM monthly_sales
    UNPIVOT (sales FOR month IN (jan, feb, mar, apr))
  ORDER BY empid;

This is very nice, but I need the month column to contain the number of the month instead of the name. I tried this, but failed with an error:

SELECT *
  FROM monthly_sales
    UNPIVOT (sales FOR month IN (jan as 1, feb as 2, mar as 3, apr as 4))
  ORDER BY empid;

I haven't found any documented way to do it, so I'll appreciate any help.

Upvotes: 1

Views: 39

Answers (2)

neeru0303
neeru0303

Reputation: 169

Aliases don't work inside FOR LOOP in UNPIVOT won't work. You can generate monthno after UNPIVOT. Something like this


CREATE OR REPLACE TEMP TABLE monthly_sales(
  empid INT,
  dept TEXT,
  jan INT,
  feb INT,
  mar INT,
  apr INT
);

INSERT INTO monthly_sales VALUES
  (1, 'electronics', 100, 200, 300, 100),
  (2, 'clothes', 100, 300, 150, 200),
  (3, 'cars', 200, 400, 100, 50),
  (4, 'appliances', 100, NULL, 100, 50);



WITH UNPIVOT_INFO AS (
    SELECT
    *
    FROM MONTHLY_SALES
    UNPIVOT (SALES FOR MONTH IN (JAN, FEB, MAR, APR))
    ORDER BY EMPID, 1
)
SELECT
    EMPID,
    DEPT,
    SALES,
    DECODE(
        MONTH,
        'JAN', 1,
        'FEB', 2,
        'MAR', 3,
        'APR', 4,
        'MAY', 5,
        'JUN', 6,
        'JUL', 7,
        'AUG', 8,
        'SEP', 9,
        'OCT', 10,
        'NOV', 11,
        'DEC', 12
    ) AS MONTHNO
FROM UNPIVOT_INFO;

Upvotes: 0

marcothesane
marcothesane

Reputation: 6721

Shouldn't this work?

SELECT 
  CASE month
    WHEN jan THEN  1
    WHEN feb THEN  2
    WHEN mar THEN  3
    WHEN apr THEN  4
    ELSE 'other'
  END AS monthno
, *
FROM monthly_sales
UNPIVOT (sales FOR month IN (jan as 1, feb as 2, mar as 3, apr as 4))
ORDER BY empid, 1;

Upvotes: 0

Related Questions