`

SQL Server中的行列转换问题

阅读更多

 

SQL Server中的行列转换问题
 
普通行列转换仅针对sql server 2000提供静态和动态写法。
 
增加sql server 2005的有关写法。
 

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

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

完整语法:

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>)

)

 

注意:PIVOT、UNPIVOT是SQL Server 2005 的语法,使用需修改数据库兼容级别
 在数据库属性->选项->兼容级别改为   90

 
 
 
经典案例:
 
问题:假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果): 
姓名 语文 数学 物理 
---- ---- ---- ----
李四 74   84   94
张三 74   83   93
-------------------
*/
 
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
 
--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
  max(case 课程 when '语文' then 分数 else end) 语文,
  max(case 课程 when '数学' then 分数 else end) 数学,
  max(case 课程 when '物理' then 分数 else end) 物理
from tb
group by 姓名
 
--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
--变量按sql语言顺序赋值
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end
) [' + 课程 + ']'
from (select distinct 课程 from tb) as 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)

 
 
--SQL SERVER 2005 静态SQL。
select from tb pivot (max(分数) for 课程 in (语文,数学,物理)) a
 
--SQL SERVER 2005 动态SQL。
--使用isnull()
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' '') + 课程 from tb group by 课程
exec ('select * from tb pivot (max(分数) for 课程 in (' + @sql + ')) a')
 
 

--使用stuff()

declare@sqlvarchar(8000)

set@sql=''  --初始化变量@sql

select@sql=@sql+','+课程fromtbgroupby课程--变量多值赋值

set@sql=stuff(@sql,1,1,'')--去掉首个','

set@sql='select * from tb pivot (max(分数) for课程in ('+@sql+'))a'

exec(@sql)

 
-----------------------------------------------------------------------
 
/*
问题:在上述结果的基础上加平均分,总分,得到如下结果:
姓名 语文 数学 物理 平均分 总分 
---- ---- ---- ---- ------ ----
李四 74   84   94   84.00  252
张三 74   83   93   83.33  250
*/
 
--SQL SERVER 2000 静态SQL。
select 姓名 姓名,
  max(case 课程 when '语文' then 分数 else end) 语文,
  max(case 课程 when '数学' then 分数 else end) 数学,
  max(case 课程 when '物理' then 分数 else end) 物理,
  cast(avg(分数*1.0) as decimal(18,2)) 平均分,
  sum(分数) 总分
from tb
group by 姓名
 
--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ', max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end)
 [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分
 from tb group by 姓名'
exec(@sql) 
 
--SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select from (select from tb) a pivot (max(分数) for 课程 in (语文,数学,物理))
 b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分, sum(分数) 总分 from tb
 group by 姓名) n
where m.姓名 = n.姓名
 
--SQL SERVER 2005 动态SQL。
--使用isnull()
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + '))
 b) m , 
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分, sum(分数) 总分 from tb
 group by 姓名) n
where m.姓名 = n.姓名')
 
drop table tb    
 
 

 

--使用stuff()

--

declare@sqlvarchar(8000)

set@sql=''  --初始化变量@sql

select@sql=@sql+','+课程fromtbgroupby课程--变量多值赋值

--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)

 
------------------------------------------------------------------
------------------------------------------------------------------
 
/*
问题:如果上述两表互相换一下:即表结构和数据为:
姓名 语文 数学 物理
张三 74  83  93
李四 74  84  94
想变成(得到如下结果): 
姓名 课程 分数 
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
*/
 
create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go
 
--SQL SERVER 2000 静态SQL。
select from
(
 select 姓名 , 课程 = '语文' , 分数 = 语文 from tb 
 union all
 select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
 union all
 select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then when '数学' then when '物理' then 3
 end
 
--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' '' ) + ' select 姓名 , [课程] = ' 
quotename(Name '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns 
where name! = N'姓名' and ID = object_id('tb'--表名tb,不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')
 
--SQL SERVER 2005 动态SQL。
select 姓名, 课程, 分数 from tb unpivot (分数 for 课程 in([语文], [数学], [物理])) 
t
 
--SQL SERVER 2005 动态SQL,同SQL SERVER 2000 动态SQL。
 
--------------------
/*
问题:在上述的结果上加个平均分,总分,得到如下结果:
姓名 课程   分数
---- ------ ------
李四 语文   74.00
李四 数学   84.00
李四 物理   94.00
李四 平均分 84.00
李四 总分   252.00
张三 语文   74.00
张三 数学   83.00
张三 物理   93.00
张三 平均分 83.33
张三 总分   250.00
------------------
*/
 
select from
(
 select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb 
 union all
 select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb
 union all
 select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb
 union all
 select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as d
ecimal(18,2)) from tb
 union all
 select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb) t
order by 姓名 , case 课程 when '语文' then when '数学' then when '物理' then 3
 when '平均分' then when '总分' then end
 
drop table tb
 
 
 
 

 

分享到:
评论

相关推荐

    sql server 行列转换

    通过以上步骤,我们可以看到SQL Server中的行列转换功能是非常强大且灵活的。它不仅能够满足基本的数据整理需求,还能根据具体的应用场景进行定制化的处理。掌握这些技巧,对于提高数据处理效率、优化数据分析流程等...

    SQL 2008行列转换的pivot

    ### SQL 2008 行列转换 (Pivot) 的动态实现 #### 知识点一:行列转换(Pivot)的概念与应用场景 在数据库查询中,有时我们需要将数据表中的行转换为列,或者将列转换为行,这种操作被称为行列转换。行列转换在报表...

    sql查询中行列转换

    ### SQL查询中的行列转换 在数据库管理中,经常会遇到数据结构需要调整的情况,尤其是当原始数据的排列方式与实际需求不一致时。本篇文章将详细解释如何通过一条SQL查询语句实现行列转换,并且会针对两种不同的SQL ...

    Oracle SQLServer行列转换

    ### Oracle SQLServer 行列转换知识点详解 #### 一、SQLServer 行转列方法 在SQLServer中,实现行转列的操作有两种主要方法:一种是利用`CASE...WHEN`语句配合`GROUP BY`聚合函数的方式;另一种是通过`PIVOT`操作...

    sqlserver2005 行列转换实现方法

    如果你想要将多个公司的某个产品组合成一列显示,例如将所有公司的`SqlServer2005`产品数量放在同一列,可以使用Pivot操作。Pivot是SQL Server中的一个功能,用于将行转换为列。在这个例子中,我们可以先通过JOIN...

    SQLServer行列互转实现思路(聚合函数)

    在SQL Server中,行列互转是一种常见的数据处理需求,它能帮助我们以更直观的方式展示数据。本篇文章将深入探讨如何使用聚合函数Pivot和Unpivot来实现这一目标,特别是针对SQL Server数据库。 首先,让我们了解什么...

    sql server动态行转列.sql

    sqlserver 动态行专列 避免了数据列过多的时候大量的使用case when then...... 原数据 : UserName Subject Score Nick 语文 80 Nick 数学 90 Nick 英语 70 Nick 生物 85 Kent 语文 80 Kent 数学 90 Kent ...

    SQL SERVER 2005+ 版本行列转换数据脚本

    虽然题目中未明确提到行列转换的具体实现,但在实际应用场景中,如果需要对上述数据进行行列转换(例如,按月份汇总每个用户的得分),则可以使用SQL Server提供的`PIVOT`操作来实现。例如: ```sql SELECT * FROM ...

    sql动态转换行列和字符的分割

    在SQL中,行列转换是一种常见的数据操作,尤其在数据分析和报表生成时尤为关键。动态添加列则是在查询过程中根据特定条件或数据集自动生成列,这在处理不断变化的数据结构时非常有用。以下是对这些主题的详细解释: ...

    在Sql Server 数据库中利用存储过程实现动态交叉表

    在SQL Server中,这通常需要使用到PIVOT(行列转换)和UNPIVOT(行列还原)操作来实现。 存储过程允许将创建动态交叉表的逻辑代码集中存储,并可以重复调用,提高了数据操作的灵活性和效率。实现动态交叉表的关键...

    SQL 数据行列转换

    2. **FOR XML PATH() 函数的应用**:详细解释FOR XML PATH()函数在行列转换中的作用及其实现方法。 3. **动态SQL的使用**:探讨如何通过动态SQL来解决行列转换过程中遇到的问题。 4. **实际案例分析**:通过一个具体...

    SQL2000 和 SQL2005 下 行列转换 示例 - freeliver54 - 博客园.pdf

    本文档提供了在SQL Server 2000和SQL Server 2005两个版本中进行行转列操作的具体示例。行转列是一种常见的数据处理方式,用于将表格中的行数据转换为列数据,便于进一步的数据分析或展示。 #### 二、环境准备 - *...

    经典SQLServer操作脚本

    数据的行列转换在数据分析中十分常见。SQL Server提供了PIVOT和UNPIVOT操作来实现这一目的。PIVOT用于将行数据转换为列,而UNPIVOT则是相反的过程,将列数据转换为行。 5. **SQLServer2000经典脚本**: SQL ...

    SQL语句普通行列转换.docx

    本文档旨在介绍如何使用SQL Server中的行列转换功能来解决常见的数据展示需求。通过具体的示例,将原始的宽表(每一行代表一个学生的所有科目成绩)转换为长表(每一行代表一个学生的一个科目的成绩),以及相反的...

    SQL行列转换 Pivot UnPivot

    Pivot 和 UnPivot 是 SQL Server 2005 中引入的两个语法,用于实现行列转换。 Pivot Pivot 语法的主要作用是将列值旋转为列名,即行转列。其基本语法为: ``` SELECT * FROM table_source PIVOT (聚合函数(value_...

    Microsoft SQL Server 2005技术内幕全套(三):T-SQL查询.part1

    《Microsoft SQL Server 2005技术内幕全套(三):T-SQL查询》是一部深入探讨SQL Server 2005数据库管理系统中Transact-SQL(T-SQL)查询技术的专业著作。本部分主要聚焦于如何高效、准确地在SQL Server 2005环境中...

Global site tag (gtag.js) - Google Analytics