SQL 行转列查询汇总
标签：
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://haodehen.cn/did119511
SQL 行转列查询汇总
in (…) )AS P
注意：PIVOT、UNPIVOT是SQL Server 2005 的语法，使用需修改数据库兼容级别(在数据库属性->选项->兼容级别改为 90 )
SQL2008 中可以直接使用
完整语法：
table_source
PIVOT(
聚合函数（value_column）
FOR pivot_column
IN( <column_list> )
)
View Code
UNPIVOT 用于将列明转为列值（即列转行），在SQL Server 2000可以用UNION来实现
完整语法：
table_source
UNPIVOT(
value_column
FOR pivot_column
IN( <column_list> )
)
典型实例
一、行转列
1 、建立表格
IF OBJECT_ID( ‘ tb ‘ ) IS NOT NULL DROP TABLE tb
go
CREATE TABLE tb(姓名 VARCHAR( 10 ),课程 VARCHAR( 10 ),分数 INT)
insert into tb VALUES ( ‘ 张三 ‘ , ‘ 语文 ‘ , 74 )
insert into tb VALUES ( ‘ 张三 ‘ , ‘ 数学 ‘ , 83 )
insert into tb VALUES ( ‘ 张三 ‘ , ‘ 物理 ‘ , 93 )
insert into tb VALUES ( ‘ 李四 ‘ , ‘ 语文 ‘ , 74 )
insert into tb VALUES ( ‘ 李四 ‘ , ‘ 数学 ‘ , 84 )
insert into tb VALUES ( ‘ 李四 ‘ , ‘ 物理 ‘ , 94 )
go
SELECT * FROM tb
go
姓名 课程 分数
---------- ---------- -----------
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
2 、使用SQL Server 2000静态SQL
SELECT 姓名,
max(CASE 课程 WHEN ‘ 语文 ‘ THEN 分数 ELSE 0 END) 语文,
max(CASE 课程 WHEN ‘ 数学 ‘ THEN 分数 ELSE 0 END) 数学,
max(CASE 课程 WHEN ‘ 物理 ‘ THEN 分数 ELSE 0 END) 物理
FROM tb
GROUP BY 姓名
3 、使用SQL Server 2000动态SQL
-- SQL SERVER 2000动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
-- 变量按sql语言顺序赋值
declare@sqlvarchar( 500 )
set @sql= ‘ select姓名 ‘
select @sql=@sql+ ‘ ,max(case课程when ‘‘‘ +课程+ ‘‘‘ then分数else 0 end)[ ‘ +课程+ ‘ ] ‘
from (selectdistinct课程fromtb)a-- 同from tb group by课程，默认按课程名排序
set @sql=@sql+ ‘ from tb group by姓名 ‘
exec(@sql)
-- 使用isnull(),变量先确定动态部分
declare@sqlvarchar( 8000 )
select @sql=isnull(@sql+ ‘ , ‘ , ‘‘ )+ ‘ max(case课程when ‘‘‘ +课程+ ‘‘‘ then分数else 0 end) [ ‘ +课程+ ‘ ] ‘
from (selectdistinct课程fromtb)asa
set @sql= ‘ select姓名, ‘ +@sql+ ‘ from tb group by姓名 ‘
exec(@sql)
4 、使用SQL Server 2005静态SQL
SELECT * FROM tb pivot( MAX(分数) FOR 课程 IN (语文,数学,物理))a
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
5 、使用SQL Server 2005动态SQL
-- 使用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)
二、行转列结果加上总分、平均分
1 、使用SQL Server 2000静态SQL
-- SQL SERVER 2000静态SQL
select姓名,
max(case课程when ‘ 语文 ‘ then分数else0end)语文,
max(case课程when ‘ 数学 ‘ then分数else0end)数学,
max(case课程when ‘ 物理 ‘ then分数else0end)物理,
sum(分数)总分,
cast(avg(分数 * 1.0 )asdecimal( 18 , 2 ))平均分
fromtb
groupby姓名
姓名 语文 数学 物理 总分 平均分
---------- ----------- ----------- ----------- -----------
李四 74 84 94 252 84.00
张三 74 83 93 250 83.33
2 、使用SQL Server 2000动态SQL
-- SQL SERVER 2000动态SQL
declare@sqlvarchar( 500 )
set @sql= ‘ select姓名 ‘
select @sql=@sql+ ‘ ,max(case课程when ‘‘‘ +课程+ ‘‘‘ then分数else 0 end)[ ‘ +课程+ ‘ ] ‘
from (selectdistinct课程fromtb)a
set @sql=@sql+ ‘ ,sum(分数)总分,cast(avg(分数*1.0) as decimal(18,2)) 平均分from tb group by姓名 ‘
exec(@sql)
3 、使用SQL Server 2005静态SQL
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.姓名
4 、使用SQL Server 2005动态SQL
-- 使用stuff()
DECLARE @sql VARCHAR( 8000 )
SET @sql = ‘‘
-- 初始化变量@sql
SELECT @sql = @sql + ‘ , ‘ + 课程
FROM tb
GROUP BY 课程
-- 变量多值赋值
--同select @sql = @sql + ‘ , ‘ +课程 from ( select distinct 课程 from tb)a
SET @sql = STUFF(@sql, 1 , 1 , ‘‘ )
--去掉首个 ‘ , ‘
SET @sql = ‘ select m.* , n.总分,n.平均分 from
( select * from ( select * from tb) a pivot (max(分数) for 课程 in ( ‘ + @sql
+ ‘ )) b) m ,
( select 姓名,sum(分数)总分, cast(avg(分数* 1.0 ) as decimal ( 18 , 2 )) 平均分 from tb group by 姓名) n
where m.姓名= n.姓名 ‘
EXEC(@sql)
-- 或使用isnull()
DECLARE @sql VARCHAR( 8000 )
SELECT @sql = ISNULL(@sql + ‘ , ‘ , ‘‘ ) + 课程
FROM tb
GROUP BY 课程
SET @sql = ‘ select m.* , n.总分,n.平均分 from
( select * from ( select * from tb) a pivot (max(分数) for 课程 in ( ‘ + @sql
+ ‘ )) b) m ,
( select 姓名,sum(分数)总分, cast(avg(分数* 1.0 ) as decimal ( 18 , 2 )) 平均分 from tb group by 姓名) n
where m.姓名= n.姓名 ‘
EXEC(@sql)
二、列转行
1 、建立表格
IF OBJECT_ID( ‘ tb ‘ ) IS NOT NULL DROP TABLE tb
go
CREATE TABLE tb(姓名 VARCHAR( 10 ),语文 INT,数学 INT,物理 INT)
INSERT INTO tb VALUES( ‘ 张三 ‘ , 74 , 83 , 93 )
INSERT INTO tb VALUES( ‘ 李四 ‘ , 74 , 84 , 94 )
go
SELECT * FROM tb
姓名 语文 数学 物理
---------- ----------- ----------- -----------
张三 74 83 93
李四 74 84 94
2 、使用SQL Server 2000静态SQL
-- SQL SERVER 2000静态SQL。
select * from
(
select姓名,课程 = ‘ 语文 ‘ ,分数= 语文fromtb
unionall
select姓名,课程 = ‘ 数学 ‘ ,分数= 数学fromtb
unionall
select姓名,课程 = ‘ 物理 ‘ ,分数= 物理fromtb
) t
orderby姓名,case课程when ‘ 语文 ‘ then1when ‘ 数学 ‘ then2when ‘ 物理 ‘ then3end
姓名 课程 分数
---------- ---- -----------
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
2 、使用SQL Server 2000动态SQL
-- SQL SERVER 2000动态SQL。
-- 调用系统表动态生态。
declare@sqlvarchar( 8000 )
select @sql=isnull(@sql+ ‘ union all ‘ , ‘‘ )+ ‘ select姓名, [课程]= ‘
+quotename(Name, ‘‘‘‘ )+ ‘ , [分数] = ‘ +quotename(Name)+ ‘ from tb ‘
fromsyscolumns
whereName != ‘ 姓名 ‘ andID=object_id( ‘ tb ‘ )-- 表名tb，不包含列名为姓名的其他列
orderbycolid
exec(@sql + ‘ order by姓名 ‘ )
go
3 、使用SQL Server 2005静态SQL
-- SQL SERVER 2005动态SQL
SELECT 姓名 ,
课程 ,
分数
FROM tb UNPIVOT ( 分数 FOR 课程 IN ( [语文], [数学], [物理] ) ) t
4 、使用SQL Server 2005动态SQL
-- 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)
阅读：31次