Search code examples
sqlpivotsnowflake-cloud-data-platform

Can I Pivot in Snowflake without single quotes, without manually aliasing the column names?


My use case is quite specific, and I couldn't find a way of doing that as for now.

I am dynamically pivoting my tables using a stored procedure, because my pivoted outputs sometimes have hundreds of different columns. The only issue that I have now is the columns have single quotes around them, and I would very much like to get rid of them in automated way.

Aliasing the columns don't work, because as I said I have hundreds of different columns for each output. I need a way of pivoting without single quotes or have an easy way of automatically renaming the columns without the quotes. Is there anything that would help me with it?

I've never worked with Python code either, but I've heard it's possible to run it in Snowflake directly. I am just looking for a way of doing that automatically.


Solution

  • From the documention on PIVOT

    If you prefer the column names without quotes, or if you prefer that the output have different column names than the input, you can include the column names in the AS clause, as shown below:

    SELECT * 
      FROM monthly_sales
        PIVOT(SUM(amount) FOR MONTH IN ('JAN', 'FEB', 'MAR', 'APR'))
          AS p (EMP_ID_renamed, JAN, FEB, MAR, APR)
      ORDER BY EMP_ID_renamed;
    
    EMP_ID_RENAMED JAN FEB MAR APR
    1 10400 8000 11000 18000
    2 39500 90700 12000 5300

    If you are dynamically building the PIVOT SQL, it should be feasible to also dynamically build the pivot column names as well.