SQL行转列与列转行技巧-CSDN博客

本文链接：https://blog.csdn.net/mss359681091/article/details/53021522

一、行转列

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)