再议PIVOT语句的一个注意点和一个问题的扩展

再议PIVOT语句的一个注意点和一个问题的扩展

问题引入：

刚才和小梁在讨论PIVOT语句行转列后的一个求和问题，结果在解决的过程中带出一个新问题来，容再下慢慢道来。

环境准备：

------------------------------------------------------------------------

-- Author: happyflystone

-- Date : 2009-03-07 20:36:05

-- Ver: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)

-- Apr 14 2006 01:12:25

-- Copyright (c) 1988-2005 Microsoft Corporation

-- Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

--

------------------------------------------------------------------------

CREATE TABLE ta(id INT,col1 Nvarchar(2),col2 Nvarchar(2),col3 Nvarchar(8),col4 INT)

;

INSERT INTO ta

SELECT 1,'HN','CS','abc',1 UNION ALL

SELECT 2,'HN','CS','abcd',2 UNION ALL

SELECT 3,'HN','CD','abcd',3 UNION ALL

SELECT 4,'HN','HY','aa' ,4

要求结果：

COL1 COL2 abc abcd ae sum_

---- ---- ----------- ----------- ----------- -----------

HN CD NULL 3 NULL 3

HN CS 1 2 NULL 3

HN HY NULL NULL 4 4

实现：

DECLARE @s varchar(8000) ,@s1 varchar(8000)

SELECT @S = ISNULL(@S + ',','')+ '['+LTRIM(COL3)+']'

FROM (SELECT DISTINCT COL3 FROM ta) A

SELECT @S1 = ISNULL(@S1 + '+','')+ 'isnull(['+LTRIM(COL3)+'],0)'

FROM (SELECT DISTINCT COL3 FROM ta) A

EXEC('WITH T

AS

(SELECT COL1,COL2,'+@S+'

FROM

(SELECT COL1,COL2,COL3,COL4

FROM TA

) P

PIVOT

( SUM (COL4)

FOR COL3 IN ('+@S+')

)AS UNPVT)

SELECT *,'+@S1+' AS [SUM]

FROM T')

-- 小梁的解法

EXEC('SELECT COL1,COL2,'+@S+','+@s1+' as sum_

FROM

(SELECT COL1,COL2,COL3,COL4

FROM ta

) P

PIVOT

( SUM (COL4)

FOR COL3 IN ('+@S+')

)AS UNPVT')

set @s = null

--随手再写一个麻烦一点的

SELECT @S = ISNULL(@S + ',','')+ '['+LTRIM(COL3)+']'

FROM (SELECT DISTINCT COL3 FROM (SELECT COL1,COL2,COL3,COL4 FROM TA

UNION ALL

SELECT COL1,COL2 ,'SUM_' AS COL3,SUM(COL4) AS COL4 FROM TA GROUP BY COL1,COL2)B) A

EXEC('SELECT COL1,COL2,'+@S+'

FROM

(SELECT COL1,COL2,COL3,COL4

FROM (SELECT COL1,COL2,COL3,COL4 FROM TA

UNION ALL

SELECT COL1,COL2 ,''SUM_'' AS COL3,SUM(COL4) AS COL4

FROM TA

GROUP BY COL1,COL2)A

) P

PIVOT

( SUM (COL4)

FOR COL3 IN ('+@S+')

)AS UNPVT')

--结果显示

/*

COL1 COL2 abc abcd ae sum

---- ---- ----------- ----------- ----------- -----------

HN CD NULL 3 NULL 3

HN CS 1 2 NULL 3

HN HY NULL NULL 4 4

COL1 COL2 abc abcd ae sum_

---- ---- ----------- ----------- ----------- -----------

HN CD NULL 3 NULL 3

HN CS 1 2 NULL 3

HN HY NULL NULL 4 4

COL1 COL2 abc abcd ae SUM_

---- ---- ----------- ----------- ----------- -----------

HN CD NULL 3 NULL 3

HN CS 1 2 NULL 3

HN HY NULL NULL 4 4

*/

在上面的写法中所有SUM后面我都加了一个‘_’,一开始的出发点是怕关键字影响语句的执行，事实上没有'_'也不影响语句的执行和结果，大家可以测试一下，所以没去掉是为了说明下面我要说的一个注意点的引入，我在去掉'_'后发现不影响语句的执行和结果，心想如果COL3里有关键字是否影响语句的执行，于是又插入一行，INSERT INTO TA SELECT 5,'HN','HY','SUM' ,5,结果仍然没问题（大家自己测试，我不再列表了），心里乐呵呵，突然我决定加一个[sum]试试，奇怪的问题出现了

Delete from ta;

go

INSERT INTO ta

SELECT 1,'HN','CS','abc',1 UNION ALL

SELECT 2,'HN','CS','abcd',2 UNION ALL

SELECT 3,'HN','CD','abcd',3 UNION ALL

SELECT 4,'HN','HY','[sum]' ,4 -- 'sum]' 改成这样语法就过不了

DECLARE @s varchar(8000) ,@s1 varchar(8000)

SELECT @S = ISNULL(@S + ',','')+ '['+LTRIM(COL3)+']'

FROM (SELECT DISTINCT COL3 FROM ta) A

exec('SELECT COL1,COL2,'+@S+'

FROM

(SELECT COL1,COL2,COL3,COL4

FROM ta

) P

PIVOT

( SUM (COL4)

FOR COL3 IN ('+@S+')

)AS UNPVT')

--结果：

/*

COL1 COL2 [sum],[abc abcd

---- ---- ----------- -----------

HN CD NULL 3

HN CS NULL 2

HN HY NULL NULL

*/

晕，这是什么玩意呀，print出SQL如下 ：

SELECT COL1,COL2,[[sum]],[abc],[abcd]

FROM

(SELECT COL1,COL2,COL3,COL4

FROM ta

) P

PIVOT

( SUM (COL4)

FOR COL3 IN ([[sum]],[abc],[abcd])

)AS UNPVT

哦，原来]]转义了，这就是一个注意点：如果数据最后包含']'时使用PIVOT一定要注意哦，怎么解决呢，估计只能使用前先替代这个字符了，如果大家有好办法，希望告诉我，感谢！！

--删除测试环境：

drop table ta