Incompatible Pivot Type SQL

Question

I have been given a database I need to perform a PIVOT on. My Pivot works perfectly fine. However, one of the columns I need to perform in my SELECT is a column of text type. However, the PIVOT command cannot use this data type. How would I get round this?

Answer 1

My personal preference would be just to convert the column type to VARCHAR(MAX) and be done with it, TEXT is on the deprecation list anyway.

If this is not an option you can simply cast/convert the text value to VARCHAR(MAX) in your query. e.g.

CREATE TABLE #T (A TEXT, B CHAR(1), Val INT);
INSERT #T (A, B, Val)
VALUES ('A', '1', 1), ('A', '2', 3), ('A', '3', 2);

SELECT  pvt.A, pvt.[1], pvt.[2], pvt.[3]
FROM    #T
        PIVOT 
        (   SUM(Val)
            FOR B IN ([1], [2], [3])
        ) pvt;

Gives the error:

Msg 488, Level 16, State 1, Line 6

Pivot grouping columns must be comparable. The type of column "A" is "text", which is not comparable.

This works fine:

SELECT  pvt.A, pvt.[1], pvt.[2], pvt.[3]
FROM    (   SELECT A = CAST(A AS VARCHAR(MAX)), B, Val
            FROM #T
        ) t
        PIVOT 
        (   SUM(Val)
            FOR B IN ([1], [2], [3])
        ) pvt;