再议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