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

问题描述

我获得了一个需要对其执行 PIVOT 的数据库.我的 Pivot 工作得非常好.但是，我需要在 SELECT 中执行的列之一是 text 类型的列.但是，PIVOT 命令不能使用这种数据类型.我该如何解决这个问题?

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?

推荐答案

我个人的喜好只是将列类型转换为 VARCHAR(MAX) 并完成它，TEXT 无论如何都在弃用列表中.

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.

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

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;

给出错误:

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

Msg 488, Level 16, State 1, Line 6

透视分组列必须具有可比性.列A"的类型是文字"，没有可比性.

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

这很好用:

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屋！