I am trying to take a file, which is structured like this:

I am trying to take a file, which is structured like this:

and turn it into a more usuable format like this:

so I am trying to utilize this query to pivot inside of snowflake, but it appears that once the data gets to december of 2024, it throws me an invalid identifier error.

SELECT 
    "User Name", 
    "User Email", 
    "Access Groups", 
    TO_DATE(column_name, 'MM/DD/YYYY') AS "Date",
    DAYNAME(TO_DATE(column_name, 'MM/DD/YYYY')) AS "Day of Week",
    value AS "Time"
FROM sandbox.hr_analytics.boston_genea_attendance
UNPIVOT (
    value FOR column_name IN (
        "6/1/2024" .........
invalid identifier error ---> "12/1/2024", "12/2/2024", "12/3/2024", "12/4/2024", "12/5/2024", "12/6/2024", 
        "12/7/2024", "12/8/2024", "12/9/2024", "12/10/2024", "12/11/2024", "12/12/2024", "12/13/2024", 
        "12/14/2024", "12/15/2024", "12/16/2024", "12/17/2024", "12/18/2024", "12/19/2024", "12/20/2024", 
        "12/21/2024", "12/22/2024", "12/23/2024", "12/24/2024", "12/25/2024", "12/26/2024", "12/27/2024", 
        "12/28/2024", "12/29/2024", "12/30/2024", "12/31/2024"
    )
);

• sql
• snowflake-cloud-data-platform

• Does the input file has data for those dates e.g 12/1/2024, 12/2/2024 etc? – samhita Commented Feb 3 at 20:36

1 Answer 1

I tried reproducing the issue creating a sample table and the same error was thrown due to missing dates.I am assuming the similar case for you that dates like 12/1/2024 ,12/2/2024 etc are missing in your file.

If thats the case, you can dynamically generate and execute the query in snowflake for ONLY the dates that are available.This is also a better approach as you would not need to hardcode the dates which is prone to error.

you can get all the date column from Information_schema like so, considering all dates columns are like '%/%/%'

SELECT LISTAGG('"' || COLUMN_NAME || '"', ', ') WITHIN GROUP (ORDER BY ORDINAL_POSITION)
INTO :col_list
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'TEST'
  AND TABLE_SCHEMA = 'TEST'
  AND TABLE_NAME = 'TEST1'
  AND COLUMN_NAME LIKE '%/%/%';

Once you get the date list you can use your UNPIVOT query to execute and get the result set

EXECUTE IMMEDIATE $$
DECLARE
    col_list STRING;
    sql_command STRING;
    OUTPUT RESULTSET;
begin

SELECT LISTAGG('"' || COLUMN_NAME || '"', ', ') WITHIN GROUP (ORDER BY ORDINAL_POSITION)
INTO :col_list
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'TEST'
  AND TABLE_SCHEMA = 'TEST'
  AND TABLE_NAME = 'TEST1'
  AND COLUMN_NAME LIKE '%/%/%';

sql_command := 'SELECT 
    "User Name", 
    "User Email", 
    "Access Groups", 
    TO_DATE(column_name, ''MM/DD/YYYY'') AS "Date",
    DAYNAME(TO_DATE(column_name, ''MM/DD/YYYY'')) AS "Day of Week",
    value AS "Time"
FROM TEST.TEST.TEST1
UNPIVOT (
    value FOR column_name IN (' || col_list || ')
);';

OUTPUT:= (EXECUTE IMMEDIATE sql_command);
RETURN TABLE(OUTPUT) ;
END;
$$
;

This returns

In case you wanted to cover a complete date range like all the dates between June-Decemeber you could still join your output to a calendar table which contains all dates for a year.