SQL SERVER行转换列及PIVOT

--*****************SQL 行转列***************************

--创建实例表
IF OBJECT_ID('TB')IS NOT NULL
 DROP TABLE TB
GO
--构建实例数据
CREATE TABLE TB(姓名 NVARCHAR(10),课程 NVARCHAR(10),分数 INT)

INSERT INTO TB VALUES(N'张三',N'语文',74)

INSERT INTO TB VALUES(N'张三',N'数学',83)

INSERT INTO TB VALUES(N'张三',N'物理',93)

INSERT INTO TB VALUES(N'李四',N'语文',74)

INSERT INTO TB VALUES(N'李四',N'数学',84)

INSERT INTO TB VALUES(N'李四',N'物理',94)

SELECT * FROM TB WITH(NOLOCK)
GO

--***************行转列***************************
--实现课程列值旋转为列名

    --使用SQL SERVER 2000静态SQL
     SELECT 
         姓名
        ,MAX(CASE 课程 
             WHEN N'语文'  THEN 分数 
             ELSE 0 
         END) AS N'语文' 
        ,MAX(CASE 课程 
             WHEN N'数学'  THEN 分数 
             ELSE 0 
         END) AS N'数学' 
        ,MAX(CASE 课程 
             WHEN N'物理'  THEN 分数 
             ELSE 0 
         END) AS N'物理'  
     FROM TB 
     GROUP BY 姓名

    --SQL SERVER 2000动态SQL,指课程不止语文、数学、物理这三门课程。

    --变量按SQL语言顺序赋值
    DECLARE @SQL1 NVARCHAR(MAX)

    SET @SQL1=N'SELECT 姓名'

    SELECT 
        @SQL1=@SQL1+N',MAX(CASE 课程 
            WHEN N'''+课程+N''' THEN 分数 
            ELSE 0 
        END)['+课程+']'  
    FROM( 
            SELECT 
                DISTINCT 课程 
            FROM TB 
         )A  
    SET @SQL1=@SQL1+N' FROM TB GROUP BY 姓名'

    EXEC(@SQL1)

    --使用ISNULL(),变量先确定动态部分
    DECLARE @SQL NVARCHAR(MAX)
    SELECT 
        @SQL=ISNULL(@SQL+',','') +N' MAX(CASE 课程 
                WHEN N'''+课程+ N''' THEN 分数 
                ELSE 0 
            END) ['+课程+N']'  
    FROM(
            SELECT 
                DISTINCT 课程 
            FROM TB
         )AS A      
    SET @SQL=N'SELECT 
                   姓名,'+@SQL+N' FROM TB GROUP BY 姓名'

    EXEC(@SQL)

    PIVOT--列值转换为列名，即行转列

    PIVOT(聚合函数(列) FOR 列 in (…) )AS P

    完整语法：

    Table_Source
    PIVOT
    (
    聚合函数【MAX,SUM,等】（value_column）
    FOR pivot_column【要成为列标题的值的列】
       IN(列值 of pivot_column)
    ) 别命

    --使用SQL SERVER 2005静态SQL

 SELECT 
     姓名
    ,语文
    ,数学
    ,物理 
 FROM TB 
 PIVOT
 ( 
      MAX(分数)FOR 课程 IN 
     ( 
          语文 
         ,数学 
         ,物理 
     ) 
 )A  

    --使用SQL Server 2005动态SQL

 --1使用STUFF( character_expression , start , length ,character_expression ),start 从索引1开始
    DECLARE @SQL2 NVARCHAR(MAX)
    SET  @SQL2=''  
    SELECT  @SQL2= @SQL2+','+课程 FROM TB GROUP BY 课程 
    PRINT @SQL2
    SET @SQL2=STUFF( @SQL2,1,1,'')--去掉首个','
    PRINT @SQL2
    SET @SQL2=N'SELECT * FROM TB PIVOT (MAX(分数) FOR 课程 IN ('+ @SQL2+'))A'
    EXEC( @SQL2)

    --2使用ISNULL()

    DECLARE  @SQL3 NVARCHAR(MAX)
    SELECT  @SQL3=ISNULL( @SQL3+',','')+课程 FROM TB GROUP BY 课程           
    SET @SQL3=N'SELECT * FROM TB PIVOT (MAX(分数) FOR 课程 IN ('+ @SQL3+'))A'
    EXEC( @SQL3)

/*运行结果：
    姓名       语文        数学        物理
    ---------- ----------- ----------- -----------
    张三       74          83          93
    李四       74          84          94
*/

--行转列结果加上总分、平均分

    --SQL SERVER 2000静态SQL
    BEGIN
        SELECT 
            姓名 
           ,MAX(CASE 课程 
                WHEN N'语文'  THEN 分数 
                ELSE 0 
            END) 数学 
           ,MAX(CASE 课程 
                WHEN N'数学'  THEN 分数 
                ELSE 0 
            END) 物理 
           ,MAX(CASE 课程 
                WHEN N'物理'  THEN 分数 
                ELSE 0 
            END) 语文 
           ,SUM(分数) AS 总分
           ,CAST(AVG(分数*1.0) AS DECIMAL(4,2)) AS 平均分
        FROM TB 
        GROUP BY 姓名
    END

    --SQL SERVER 2000动态SQL
    BEGIN
        DECLARE @SQL4 nvarchar(MAX)
        SELECT 
            @SQL4=ISNULL(@SQL4+',','')+N' MAX(CASE 课程 WHEN N'''+课程+N''' THEN 分数 ELSE 0 END)' +课程
        FROM (SELECT 
                  DISTINCT  课程
              FROM TB 
           ) AS TT
         SET @SQL4=N'SELECT 
                         姓名
                        ,'+@SQL4++N',SUM(分数)AS 总分
                        ,CAST(AVG(分数*1.0)AS decimal(4,2)) AS 平均分 
                     FROM TB 
                     GROUP BY 姓名'
        PRINT @SQL4
        EXEC(@sql4)
    END

    --SQL SERVER 2005 静态PIVOT
    BEGIN
        SELECT 
            A.*
           ,B.总分
           ,B.平均分 
        FROM (
                SELECT 
                    * 
                FROM TB 
                PIVOT
                ( 
                     MAX(分数) FOR 课程 IN 
                    ( 
                         数学 
                        ,语文 
                        ,物理 
                    ) 
                ) AS TT
             ) AS A 
        INNER JOIN (
            SELECT 
                姓名
               ,SUM(分数) AS 总分 
               ,CAST(AVG(分数*1.0) AS decimal(4,2)) AS 平均分 
            FROM TB 
            GROUP BY 姓名
         ) AS B 
            ON A.姓名=B.姓名
    END

    --SQL SERVER 2005 动态PIVOT
    BEGIN
        DECLARE @SQL5 NVARCHAR(MAX)
        SELECT 
            @SQL5=ISNULL(@SQL5+ ',','')+ 课程 
        FROM TB  
        GROUP BY 课程
        SET @SQL5=N'SELECT * FROM TB PIVOT (MAX(分数) FOR 课程 IN ('+@SQL5+')) AS TT'
        SET @SQL5=N'SELECT A.*,B.总分,B.平均分 FROM ('+@SQL5+N') AS A 
        INNER JOIN (SELECT 姓名,SUM(分数) AS 总分,CAST(AVG(分数) AS DECIMAL(4,2)) AS 平均分 FROM TB GROUP BY 姓名) AS B 
        ON A.姓名=B.姓名'
                PRINT @SQL5
        EXEC SP_EXECUTESQL @SQL5
    END

/*运行结果：
    姓名  语文 数学 物理 总分  平均分
    ----------------- ----------- ----
    李四  74   84   94   252   84.00
    张三  74   83   93   250   83.33
*/

 

--另一个例子

--Create 样例表
IF OBJECT_ID('TEST') IS NOT NULL
BEGIN
    DROP TABLE TEST
END

CREATE TABLE TEST
(
     ID INT
    ,Name VARCHAR(20)
    ,[Quarter] INT
    ,[Profile] INT
)

--构建样例数据
INSERT INTO TEST VALUES(1,'A',1,1000)
INSERT INTO TEST VALUES(1,'A',2,2000)
INSERT INTO TEST VALUES(1,'A',3,4000)
INSERT INTO TEST VALUES(1,'A',4,5000)
INSERT INTO TEST VALUES(1,'B',1,3000)
INSERT INTO TEST VALUES(1,'B',2,3500)
INSERT INTO TEST VALUES(1,'B',3,4200)
INSERT INTO TEST VALUES(1,'B',4,5500)

--行转换列:四个季度的利润转换成横向显示

--SQL SERVER 2000 静态SQL
SELECT 
    ID 
   ,Name 
    ,SUM(CASE [Quarter] 
        WHEN 1 THEN [Profile]
        ELSE 0 
    END) AS N'1季度' 
    ,SUM(CASE [Quarter] 
            WHEN 2 THEN [Profile]
            ELSE 0 
        END) AS N'2季度' 
    ,SUM(CASE [Quarter] 
            WHEN 3 THEN [Profile]
            ELSE 0 
        END) AS N'3季度' 
    ,SUM(CASE [Quarter] 
            WHEN 4 THEN [Profile]
            ELSE 0 
        END) AS N'4季度' 
FROM TEST 
GROUP BY ID,Name

--SQL SERVER 2005 静态PIVOT
SELECT 
   *
FROM TEST 
PIVOT
(
     SUM([Profile]) FOR [Quarter] IN ([1],[2],[3],[4])
) AS TT