SQL中PIVOT和UNPIVOT行列转换

DECLARE @sql_col VARCHAR(8000);

DECLARE @sql_str VARCHAR(8000);

DECLARE @sql_ VARCHAR(MAX);

SELECT  @sql_col = ISNULL(@sql_col + ',', '')

        + QUOTENAME(BBCAccount.dbo.BusinessType.Name)

FROM    BBCAccount.dbo.BusinessType

WHERE   ParentCode IS NULL

        AND Type = 0

        AND IsSystem = 1;

SET @sql_ = 'select *

from(

		select Code,Name  from BBCAccount.dbo.BusinessType WHERE ParentCode IS NULL AND Type=0 AND IsSystem=1

	)as tw

pivot( max(Code) for Name in(' + @sql_col + ') )piv ';

EXEC(@sql_);

明显，UN这个前缀表明了，它做的操作是跟PIVOT相反的，即列转行。UNPIVOT操作涉及到以下三个逻辑处理阶段。

1，生成副本

2，提取元素

3，删除带有NULL的行

UNPIVOT实例

CREATE TABLE pvt

    (

      VendorID INT ,

      Emp1 INT ,

      Emp2 INT ,

      Emp3 INT ,

      Emp4 INT ,

      Emp5 INT

    );

GO

INSERT  INTO pvt

VALUES  ( 1, 4, 3, 5, 4, 4 );

INSERT  INTO pvt

VALUES  ( 2, 4, 1, 5, 5, 5 );

INSERT  INTO pvt

VALUES  ( 3, 4, 3, 5, 4, 4 );

INSERT  INTO pvt

VALUES  ( 4, 4, 2, 5, 5, 4 );

INSERT  INTO pvt

VALUES  ( 5, 5, 1, 5, 5, 5 );

GO

--Unpivot the table.

SELECT  VendorID ,

        Employee ,

        Orders

FROM    ( SELECT    VendorID ,

                    Emp1 ,

                    Emp2 ,

                    Emp3 ,

                    Emp4 ,

                    Emp5

          FROM      pvt

        ) p UNPIVOT

   ( Orders FOR Employee IN ( Emp1, Emp2, Emp3, Emp4, Emp5 ) )AS unpvt;

GO