不兼容的枢轴类型SQL [英] Incompatible Pivot Type SQL

问题描述

我已经获得一个数据库，我需要对它执行 PIVOT .我的数据透视效果很好.但是，我需要在SELECT中执行的一列是 text 类型的列.但是，PIVOT命令不能使用此数据类型.我将如何解决这个问题?

解决方案

我个人的喜好是将列类型转换为VARCHAR(MAX)并使用它来完成，TEXT仍然在弃用列表中.

如果这不是一个选项，则可以在查询中简单地将文本值转换/转换为VARCHAR(MAX).例如

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;

给出错误:

第488级消息，状态1，第6行

数据透视表分组列必须具有可比性.列"A"的类型是文本"，这是不可比较的.

这很好:

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;

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?

解决方案

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;

这篇关于不兼容的枢轴类型SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！