sql - Why is my pivoting query erroring out once I hit december dates? - Stack Overflow

admin管理员组

文章数量:1305717

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

User Name	User Email	Access Groups	Building Access Groups	Column1	6/1/2024	6/2/2024	6/3/2024	6/4/2024	6/5/2024	6/6/2024

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

User Name	User Email	Access Groups	Building Access Groups	Column1	6/1/2024	6/2/2024	6/3/2024	6/4/2024	6/5/2024	6/6/2024

and turn it into a more usuable format like this:

User Name	User Email	Access Groups	Date	Day of Week	Time

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

Share Improve this question Follow edited Feb 3 at 20:34 NidenK asked Feb 3 at 20:15 NidenKNidenK 36911 silver badge1111 bronze badges 1

• 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

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

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.

本文标签： sqlWhy is my pivoting query erroring out once I hit december datesStack Overflow