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?

Does this answer help: http://stackoverflow.com/questions/10025934/how-to-pivot-text-columns-in-sql-server — Steph Locke, Apr 30 '14 at 08:49
`PIVOT` aggregates the data it shows, aggregations are forbidden on `text`. Show your code, and explain why you need to have a `text` column in a pivotted table. A workaround would be to cast the `text` to a `varchar`, but it obviously has its performance penalty. — Luaan, Apr 30 '14 at 08:49

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

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;

Incompatible Pivot Type SQL

1 Answers1

Linked

Related