SQL行转列汇总

SQL⾏转列汇总
SQL⾏转列汇总
⼀. 基础语法:
PIVOT ⽤于将列值旋转为列名（即⾏转列），在 SQL Server 2000可以⽤聚合函数配合CASE语句实现
PIVOT 的⼀般语法是：PIVOT(聚合函数(列) FOR 列 in (…) )AS P
注意：PIVOT、UNPIVOT是SQL Server 2005 的语法，使⽤需修改数据库兼容级别(在数据库属性->选项->兼容级别改为 90 ) SQL2008 中可以直接使⽤
完整语法：
table_source
PIVOT(
聚合函数（value_column）
FOR pivot_column
IN(<column_list>)
)
UNPIVOT ⽤于将列明转为列值（即列转⾏），在SQL Server 2000可以⽤UNION来实现
完整语法：
table_source
UNPIVOT(
value_column
FOR pivot_column
IN(<column_list>)
)
⼆. 典型实例
2.1 ⾏转列
2.1.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.1.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 姓名
2.1.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)
2.1.4 使⽤SQL Server 2005静态SQL
SELECT * FROM tb pivot( MAX(分数) FOR 课程 IN (语⽂,数学,物理))a
姓名语⽂数学物理
李四 74 84 94
张三 74 83 93
2.1.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)
2.2 ⾏转列结果加上总分、平均分
2.2.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.2.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)
2.2.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.姓名
2.2.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)
2.3 列转⾏
2.3.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.3.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.3.3 使⽤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
2.3.4 使⽤SQL Server 2005静态SQL
--SQL SERVER 2005动态SQL
SELECT 姓名 ,
课程 ,
分数
FROM tb UNPIVOT ( 分数 FOR 课程 IN ( [语⽂], [数学], [物理] ) ) t
2.3.5 使⽤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)。