SQL行转列问题总结(pivot的使用)

转载 2013年12月05日 09:08:56

1行转列

 

---1、最简单的行转列

/*  

 

问题:假设有张学生成绩表(tb)如下:

姓名课程分数

张三语文74

张三数学83

张三物理93

李四语文74

李四数学84

李四物理94

 

 

想变成(得到如下结果)

姓名语文数学物理

李四74   84   94

张三74   83   93

*/

--测试用

IF OBJECT_ID('[tb]')IS NOT NULL DROP TABLE[tb]

GO

create tabletb(姓名varchar(10), 课程 varchar(10), 分数 int)

insert intotb values('张三', '语文', 74)

insert intotb values('张三', '数学', 83)

insert intotb values('张三', '物理', 93)

insert intotb values('李四', '语文', 74)

insert intotb values('李四', '数学', 84)

insert intotb values('李四', '物理', 94)

go

 

--SQL SERVER2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)

declare @sql varchar(8000)

set @sql= 'select 姓名'

select @sql= @sql + ' , max(case 课程when'''+ 课程 + ''' then 分数else0 end) ['+ 课程 + ']'

from (selectdistinct 课程 from tb)asa

set @sql= @sql + ' from tb group by 姓名'

exec(@sql)

--通过动态构建@sql,得到如下脚本

select 姓名as 姓名 ,

  max(case课程 when '语文' then 分数 else 0end)语文,

  max(case课程 when '数学' then 分数 else 0end)数学,

  max(case课程 when '物理' then 分数 else 0end)物理

from tb

group by姓名

 

--SQL SERVER2005 动态SQL

declare @sql varchar(8000)

select @sql= isnull(@sql+ '],[' , '') + 课程 from tb groupby课程

set @sql= '[' + @sql + ']'

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

--得到SQLSERVER 2005静态SQL

select *from (select* from tb) a pivot(max(分数)for 课程 in(语文,数学,物理))b

 

--查询结果

/*

姓名       数学         物理         语文        

--------------------- ----------- -----------

李四        84          94          74

张三        83          93          74

 

(所影响的行数为2行)

*/

 

 

--2 加合计

/*

问题:在上述结果的基础上加平均分,总分,得到如下结果:

姓名语文数学物理平均分总分

---- ---- -------- ------ ----

李四74   84   94  84.00  252

张三74   83   93  83.33  250

*/

 

--SQL SERVER2000 静态SQL

select 姓名姓名,

  max(case课程 when '语文' then 分数 else 0end)语文,

  max(case课程 when '数学' then 分数 else 0end)数学,

  max(case课程 when '物理' then 分数 else 0end)物理,

  cast(avg(分数*1.0)as decimal(18,2))平均分,

  sum(分数)总分

from tb

group by姓名

 

--SQL SERVER2000 动态SQL

declare @sql varchar(8000)

set @sql= 'select 姓名'

select @sql= @sql + ' , max(case 课程when'''+ 课程 + ''' then 分数else0 end) ['+ 课程 + ']'

from (selectdistinct 课程 from tb)asa

set @sql= @sql + ' , cast(avg(分数*1.0)as decimal(18,2))平均分, sum(分数)总分from tb group by 姓名'

exec(@sql)

 

--SQL SERVER2005 静态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 groupby姓名) n

where m.姓名= n.姓名

 

--SQL SERVER2005 动态SQL

declare @sql varchar(8000)

select @sql= isnull(@sql+ ',' , '') + 课程 from tb groupby课程

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(分数)总分fromtb group by 姓名) n

where m.姓名= n.姓名')

 

其他实例

 

http://topic.csdn.net/u/20100708/18/55df5a90-27a7-4452-a69a-27f735539a1f.html?seed=24842417&r=66831902#r_66831902

 

 

--3、不同数据按照序号转为列,方法基本同1

 

if object_id('tb1')is not null drop tabletb1

go

CREATE tabletb1 --数据表

(

cpici varchar(10)not null,

cname varchar(10)not null,

cvalue intnull

)

--插入测试数据

INSERT INTOtb1 values('T501','x1',31)

INSERT INTOtb1 values('T501','x1',33)

INSERT INTOtb1 values('T501','x1',5)

 

INSERT INTOtb1 values('T502','x1',3)

INSERT INTOtb1 values('T502','x1',22)

INSERT INTOtb1 values('T502','x1',3)

 

INSERT INTOtb1 values('T503','x1',53)

INSERT INTOtb1 values('T503','x1',44)

INSERT INTOtb1 values('T503','x1',50)

INSERT INTOtb1 values('T503','x1',23)

 

 

--sqlserver2000里需要用自增辅助

alter tabletb1 add id int identity

go

declare @s varchar(8000)

set @s='select cpici '

select @s=@s+',max(case when rn='+ltrim(rn)+' then cvalue end) ascvlue'+ltrim(rn)

from (selectdistinct rn from (selectrn=(selectcount(1)from tb1 where cpici=t.cpiciand id<=t.id)from tb1 t)a)t

set @s=@s+' from (select rn=(selectcount(1) from tb1 where cpici=t.cpici and id<=t.id),* from tb1 t

) t group bycpici'

 

exec(@s)

go

alter tabletb1 drop column id

 

--再就可以用row_number

declare @s varchar(8000)

set @s='select cpici '

select @s=@s+',max(case when rn='+ltrim(rn)+' then cvalue end) ascvlue'+ltrim(rn)

from (selectdistinct rn from (selectrn=row_number()over(partitionby cpici order by getdate())from tb1)a)t

set @s=@s+' from (selectrn=row_number()over(partition by cpici order by getdate()),* from tb1

) t group bycpici'

 

exec(@s)

 

---结果

/*

cpici      cvlue1      cvlue2      cvlue3      cvlue4

--------------------- ----------- ----------- -----------

T501       31         33          5           NULL

T502       3           22          3           NULL

T503       53          44          50          23

警告: 聚合或其他SET 操作消除了空值。

 

(3 行受影响)

 

*/

 

 

--测试用

IF OBJECT_ID('[tb]')IS NOT NULL DROP TABLE[tb]

GO

create tabletb(电话号码varchar(15),通话时长 int ,行业 varchar(10))

insert tb

select '13883633601', 10,'餐饮'union all

select '18689704236', 20,'物流'union all

select '13883633601', 20,'物流'union all

select '13883633601', 20,'汽车'union all

select '18689704236', 20,'医疗'union all

select '18689704236', 20,'it' union all

select '18689704236', 20,'汽车'union all

select '13883633601', 50,'餐饮'

go

 

declare @sql varchar(8000)

set @sql='select电话号码,sum(通话时长)通话总和'

select @sql=@sql+',max(case when rowid='+ltrim(rowid)+' then 行业else'''' end) as [行业'+ltrim(rowid)+']'

from (selectdistinct rowid from (select (select count(distinct行业) from tb where电话号码=t.电话号码and 行业<=t.行业)rowid

from tbt) a) b

set @sql=@sql+' from ( select * , (selectcount(distinct行业) from tb where 电话号码=t.电话号码and行业<=t.行业)rowid

from tb t ) tgroup by 电话号码'

exec(@sql)

 

--结果

/*

 

(所影响的行数为8行)

 

电话号码          通话总和       行业       行业      行业       行业      

-------------------------- ---------- ---------- ---------- ----------

13883633601     100        餐饮        汽车       物流       

18689704236     80         it         汽车        物流       医疗

 

(所影响的行数为2行)

 

*/

 

另一种动态行转列:

 

http://topic.csdn.net/u/20100612/10/4CFCB667-89FA-4985-90D5-B8A420A6FF12.html

 

if object_id('[tb]')is not null drop table[tb]

go  

create table[tb]([姓名]varchar(1),[部门]varchar(4),[学历]varchar(4),[出生年月]datetime)

insert [tb]

select 'A','后勤','高中','1986-1-1'union all

select 'B','后勤','初中','1984-3-7'union all

select 'C','管理','本科','1987-2-1'union all

select 'D','操作','专科','1976-2-1'union all

select 'E','操作','专科','1943-2-1'  

go

 

 

GO

if object_id('GetGroupByCol')is not null drop procGetGroupByCol

go

create  PROCEDURE [dbo].[GetGroupByCol]

@colm nvarchar(100)

  AS

declare @sql varchar(4000)

 

set @sql='

declare @sqlvarchar(8000)

set@sql=''select 部门''

select @sql=@sql+ '', sum(case ltrim('+@colm+')when ''''''+ltrim('+@colm + ')+'''''' then 1 else 0 end)

[''+ltrim(' + @colm + ')+'']'' from (select distinct '+@colm+' from tb where '+@colm+' is not null) as a

set @sql = @sql+ '' from tb group by 部门''

exec(@sql)'

 

exec(@sql)

GO

 

exec GetGroupByColN'学历'

exec GetGroupByColN'出生年月'

exec GetGroupByColN'姓名'

 

/*

 

(所影响的行数为5行)

 

部门  本科         初中        高中         专科        

--------------- ----------- ----------- -----------

操作  0           0           0           2

管理  1           0           0           0

后勤  0           1           1           0

 

(所影响的行数为3行)

 

部门  02  1 1943 12:00AM 02  1 1976 12:00AM 03  7 1984 12:00AM 01  1 1986 12:00AM 02  1 1987 12:00AM

---------------------- ------------------ ------------------ ------------------------------------

操作  1                  1                  0                  0                  0

管理  0                  0                  0                  0                  1

后勤  0                  0                  1                  1                  0

 

(所影响的行数为3行)

 

部门  A           B           C           D           E          

--------------- ----------- ----------- ----------- -----------

操作  0           0           0           1          1

管理  0           0           1           0           0

后勤  1           1           0           0           0

 

(所影响的行数为3行)

*/

如何用MYSQL 做 Pivot Table?

"pivot table" or a "crosstab report"(Note: this page needs to be wikified)SQL Characteristic Functio...

oracle关键字pivot行转列【坑爹的三小时,动脑经真累 】

首先感谢提供Oracle的行列转换丝路!!!
  • xb12369
  • xb12369
  • 2014年09月25日 18:18
  • 34656

Oracle 11g 行列互换 pivot 和 unpivot 说明

针对Oracle 11g 之前版本的行列转换,之前整理过一篇文档:Oracle 行列转换 总结http://blog.csdn.net/tianlesoftware/article/details/4...

绑定透视表(行转列)

public DataTable PivotTable(DataTable source) { DataTable dest = new DataTable("Pivoted" + source.Ta...

Delphi7高级应用开发随书源码

  • 2003年04月30日 00:00
  • 676KB
  • 下载

关于oracle采用pivot函数列转行后一些列的值为null处理

假设现在有三张表,一张类别表,一张员工表,一张交易明细表create table kinds( name varchar2(32) ,code char(2)); insert into kinds(...

Oracle 11g Pivot函数实现列转行

先上语法规范: SELECT ....FROM    PIVOT     (      aggregate-function()      FOR  IN (, ,..., )        ) A...

maven windows下安装和使用 已经几个命令说明

①下载 http://maven.apache.org/download.cgi# zip是windows文件,gz是linux文件,所以下载apache-maven-3.3.9-bin.zip ...
  • kkgbn
  • kkgbn
  • 2016年05月10日 11:17
  • 1690

JAVA的泛型 && JAVA的几种for循环方式

泛型的本质是参数化类型,也就是说所操作的数据类型被指定为一个参数。这种参数类型可以用在类、接口和方法的创建中,分别称为泛型类、泛型接口、泛型方法。         在没有泛型的情况的下,通过对类型Ob...

Oracle 行转列pivot 、列转行unpivot 的Sql语句总结

Oracle 行转列、列转行 的Sql语句总结
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL行转列问题总结(pivot的使用)
举报原因:
原因补充:

(最多只允许输入30个字)