SQL 行转列、列转行

本文已参与「新人创作礼」活动，一起开启掘金创作之路。

一、行转列

PIVOT 用于将列值旋转为列名（即行转列），在 SQL Server 2000可以用聚合函数配合CASE语句实现

PIVOT 的一般语法是：PIVOT(聚合函数(列) FOR 列 in (…) )AS P

1、格式

table_source
PIVOT(
聚合函数（value_column）
FOR pivot_column
IN(<column_list>)
)

2、实例

(1)静态方式

SELECT  m.* ,
        n.总分 ,
        n.平均分
FROM    ( SELECT    *
          FROM      tb PIVOT( MAX(分数) FOR 课程 IN ( 语文, 数学, 物理 ) ) a
        ) m ,
        ( SELECT    姓名 ,
                    SUM(分数) 总分 ,
                    CAST(AVG(分数 * 1.0) AS DECIMAL(18, 2)) 平均分
          FROM      tb
          GROUP BY  姓名
        ) n
WHERE   m.姓名 = n.姓名

(2)动态方式

--使用stuff()

DECLARE @sql VARCHAR(8000)
 
SET @sql=''  --初始化变量 @sql
 
SELECT @sql= @sql+',' + 课程 FROM tb GROUP BY 课程 --变量多值赋值
 
SET @sql= STUFF(@sql,1,1,'')--去掉首个','
 
SET @sql='select * from tb pivot (max(分数) for 课程 in ('+@sql+'))a'
 
PRINT @sql
 
exec(@sql)
 
--或使用isnull()
 
DECLARE @sql VARCHAR(8000)
 
--获得课程集合
 
SELECT @sql= ISNULL(@sql+',','')+课程 FROM tb
GROUP BY 课程           
 
SET @sql='select * from tb pivot (max(分数) for 课程 in ('+@sql+'))a'
 
exec(@sql)

二、列转行

UNPIVOT 用于将列明转为列值（即列转行），在SQL Server 2000可以用UNION来实现

1、格式

table_source
UNPIVOT(
value_column
FOR pivot_column
IN(<column_list>)
)

2、实例

（1）静态

--SQL SERVER 2005动态SQL

SELECT  姓名 ,
        课程 ,
        分数
FROM    tb UNPIVOT ( 分数 FOR 课程 IN ( [语文], [数学], [物理] ) ) t
  （2）动态
--SQL SERVER 2005动态SQL
 
DECLARE @sql NVARCHAR(4000)
 
SELECT  @sql = ISNULL(@sql + ',', '') + QUOTENAME(name)
FROM    syscolumns
WHERE   id = OBJECT_ID('tb')
        AND name NOT IN ( '姓名' )
ORDER BY colid
 
SET @sql = 'select 姓名,[课程],[分数] from tb unpivot ([分数] for [课程] in(' + @sql
    + '))b'
 
EXEC(@sql)