The type of column ‘xxx’ conflicts with the type of other columns in the UNPIVOT list.

UNPIVOT is a SQL standard function that allows users to transform columns into rows, and Snowflake also supports it. Please see below example on how to use it:

CREATE OR REPLACE TABLE student_score
(stu_id int, math int, english int, physics int, chemist int);

INSERT INTO student_score VALLUES
    (1, 100, 90, 89, 95),
    (2, 92, 79, 90, 99),
    (3, 78, 84, 79, 93);

-- UNPIVOT example
SELECT * FROM student_score
    UNPIVOT(score FOR scores IN (math, english, physics, chemist))
    ORDER BY stu_id;

You can see that the result turned the scores that were in each column of a row into seperate rows that still associated with original student ID.

This query works well for the columns defined in UNPIVOT function to have the same column types. If they are not, however, then the UNPIVOT function will fail with error message mentioned in the title of this post.

See example below, where I changed the column type for “math” subject to float, rather than int. If I run the same UNPIVOT function now, it will fail:

CREATE OR REPLACE TABLE student_score
(stu_id int, math float, english int, physics int, chemist int);

INSERT INTO student_score VALLUES
    (1, 100, 90, 89, 95),
    (2, 92, 79, 90, 99),
    (3, 78, 84, 79, 93);

-- UNPIVOT example
SELECT * FROM student_score
    UNPIVOT(score FOR scores IN (math, english, physics, chemist))
    ORDER BY stu_id;

SQL compilation error: The type of column 'ENGLISH' conflicts with 
the type of other columns in the UNPIVOT list.

It complained about column “ENGLISH” was because it encountered “MATH” column first and “ENGLISH” did not math with the first one that it saw.

To workaround the issue, we have to setup a SUBQUERY to CAST the offending column so that ALL columns have the same data type.

-- UNPIVOT example
SELECT * FROM (
  SELECT stu_id, CAST(math as int) as math, english, physics, chemist 
  FROM student_score
)
UNPIVOT(score FOR scores IN (math, english, physics, chemist))
ORDER BY stu_id;

This should help to avoid the type conflict error when using UNPIVOT function.