读书人
栏目▼

求1 SQL语句　行转列

发布时间： 2012-12-29 10:28:09 作者： rapoo

求一 SQL语句　，行转列
查询语句：

select c.ZD 诊断,a.LB 费用类别,a.HZJE 费用金额 from SRCXB_ZYBR a,NHBBD b ,CYZMS c
where a.ZYH=b.ZYH and a.ZYH=c.ZYH
order by c.ZD,a.LB

结果：
诊断 费用类别 费用金额
白内障130.00
白内障175.00
白内障190.00
白内障175.00
白内障175.00
白内障175.00
白内障190.00
白内障175.00
白内障175.00
白内障190.00
白内障1105.00
白内障212.00
白内障212.00
白内障210.00
白内障210.00
白内障210.00
白内障28.00
白内障28.00
白内障210.00
白内障210.00
白内障210.00
白内障214.00
白内障336.00
白内障3122.00
白内障3120.00
白内障3120.00
白内障338.00
白内障335.00
白内障335.00
白内障337.00
白内障335.00
白内障3121.00
白内障3120.00
白内障4105.50
…………
膀胱恶性肿瘤130.00
膀胱恶性肿瘤1150.00
膀胱恶性肿瘤1330.00
膀胱恶性肿瘤160.00
膀胱恶性肿瘤1120.00
膀胱恶性肿瘤216.00
膀胱恶性肿瘤216.00
膀胱恶性肿瘤228.00
膀胱恶性肿瘤218.00
膀胱恶性肿瘤24.00
膀胱恶性肿瘤325.00
膀胱恶性肿瘤3334.00
膀胱恶性肿瘤4152.00
膀胱恶性肿瘤449.00
膀胱恶性肿瘤429.00
膀胱恶性肿瘤429.00
膀胱恶性肿瘤4313.50
膀胱恶性肿瘤5277.00

要求实现如下：

诊断 类别1金额合计 类别2金额合计 类别3金额合计
白内障
膀胱恶性肿瘤

……


[解决办法]
select 诊断=c.ZD ,
类别1金额合计=sum(case a.LB when 1 then a.HZJE else 0 end),
类别2金额合计=sum(case a.LB when 2 then a.HZJE else 0 end),
类别3金额合计=sum(case a.LB when 3 then a.HZJE else 0 end)
from SRCXB_ZYBR a,NHBBD b ,CYZMS c
where a.ZYH=b.ZYH and a.ZYH=c.ZYH
group by c.ZD

[解决办法]
http://topic.csdn.net/u/20111009/16/22dee7d1-001e-422a-92cb-66bd14f05e30.html
参考下这个看看
[解决办法]


create table emi
(诊断 varchar(16), 费用类别 int, 费用金额 decimal(5,2))
 
insert into emi 
select '白内障', 1, 30.00 union all
select '白内障', 1, 75.00 union all
select '白内障', 1, 90.00 union all
select '白内障', 1, 75.00 union all
select '白内障', 1, 75.00 union all
select '白内障', 1, 75.00 union all
select '白内障', 1, 90.00 union all
select '白内障', 1, 75.00 union all
select '白内障', 1, 75.00 union all
select '白内障', 1, 90.00 union all
select '白内障', 1, 105.00 union all
select '白内障', 2, 12.00 union all
select '白内障', 2, 12.00 union all
select '白内障', 2, 10.00 union all
select '白内障', 2, 10.00 union all
select '白内障', 2, 10.00 union all
select '白内障', 2, 8.00 union all
select '白内障', 2, 8.00 union all
select '白内障', 2, 10.00 union all
select '白内障', 2, 10.00 union all
select '白内障', 2, 10.00 union all
select '白内障', 2, 14.00 union all
select '白内障', 3, 36.00 union all 
 
select '白内障', 3, 122.00 union all
select '白内障', 3, 120.00 union all
select '白内障', 3, 120.00 union all
select '白内障', 3, 38.00 union all
select '白内障', 3, 35.00 union all
select '白内障', 3, 35.00 union all
select '白内障', 3, 37.00 union all
select '白内障', 3, 35.00 union all
select '白内障', 3, 121.00 union all
select '白内障', 3, 120.00 union all
select '白内障', 4, 105.50 union all
select '膀胱恶性肿瘤', 1, 30.00 union all
select '膀胱恶性肿瘤', 1, 150.00 union all
select '膀胱恶性肿瘤', 1, 330.00 union all
select '膀胱恶性肿瘤', 1, 60.00 union all
select '膀胱恶性肿瘤', 1, 120.00 union all
select '膀胱恶性肿瘤', 2, 16.00 union all
select '膀胱恶性肿瘤', 2, 16.00 union all
select '膀胱恶性肿瘤', 2, 28.00 union all
select '膀胱恶性肿瘤', 2, 18.00 union all
select '膀胱恶性肿瘤', 2, 4.00 union all
select '膀胱恶性肿瘤', 3, 25.00 union all
select '膀胱恶性肿瘤', 3, 334.00 union all
select '膀胱恶性肿瘤', 4, 152.00 union all
select '膀胱恶性肿瘤', 4, 49.00 union all
select '膀胱恶性肿瘤', 4, 29.00 union all
select '膀胱恶性肿瘤', 4, 29.00 union all
select '膀胱恶性肿瘤', 4, 313.50 union all
select '膀胱恶性肿瘤', 5, 277.00

--费用类别固定
select 
    [诊断]
,    sum(case when [费用类别]=1 then [费用金额] else 0 end) as [费用类别1]
,    sum(case when [费用类别]=2 then [费用金额] else 0 end)    as [费用类别2]
,    sum(case when [费用类别]=3 then [费用金额] else 0 end)    as [费用类别3]
,    sum(case when [费用类别]=4 then [费用金额] else 0 end)    as [费用类别4]
,    sum(case when [费用类别]=5 then [费用金额] else 0 end)    as [费用类别5]
from emi
group by [诊断]

--费用类别不固定
declare @sql varchar(max)
set @sql='select [诊断]'
select @sql=@sql+' ,sum(case [费用类别] when '+ convert(varchar(20),费用类别) + ' then 费用金额 else 0 end) as[费用类别'+convert(varchar(20),费用类别)+']'
from (select distinct [费用类别] from emi) as a
set @sql=@sql + ' from emi group by [诊断]'

exec (@sql)

--结果：
诊断               费用类别1                                   费用类别2                                   费用类别3                                   费用类别4                                   费用类别5 
 
---------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
白内障              855.00                                  114.00                                  819.00                                  105.50                                  0.00
膀胱恶性肿瘤           690.00                                  82.00                                   359.00                                  572.50                                  277.00

(2 行受影响)



[解决办法]
如果是Oracle，可以使用以下代码

select ZD 诊断, sum(decode(ZD, '白内障', HZJE, 0)) 类别1金额, sum(decode(ZD, '膀胱恶性肿瘤', HZJE, 0)) 类别2金额, sum(decode(ZD, '类别3', HZJE, 0)) 类别3金额
from (
select c.ZD ZD ,a.LB LB,a.HZJE HZJE from SRCXB_ZYBR a,NHBBD b ,CYZMS c
where a.ZYH=b.ZYH and a.ZYH=c.ZYH
order by c.ZD,a.LB
)

[解决办法]
SQL Server中行列转换 Pivot UnPivot



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



典型实例

一、行转列

1、建立表格

ifobject_id('tb')isnotnulldroptabletb

go

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



insertintotbvalues('张三','语文',74)

insertintotbvalues('张三','数学',83)

insertintotbvalues('张三','物理',93)

insertintotbvalues('李四','语文',74)

insertintotbvalues('李四','数学',84)

insertintotbvalues('李四','物理',94)

go

select*fromtb

go

姓名 课程 分数

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

张三 语文 74

张三 数学 83

张三 物理 93

李四 语文 74

李四 数学 84

李四 物理 94



2、使用SQL Server 2000静态SQL

--c

select姓名,

max(case课程when'语文'then分数else0end)语文,

max(case课程when'数学'then分数else0end)数学,

max(case课程when'物理'then分数else0end)物理

fromtb

groupby姓名

姓名 语文 数学 物理

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

李四 74 84 94

张三 74 83 93



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)

姓名 数学 物理 语文

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

李四 84 94 74

张三 83 93 74



4、使用SQL Server 2005静态SQL

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




5、使用SQL Server 2005动态SQL

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



--或使用isnull()

declare@sqlvarchar(8000)

-获得课程集合

select@sql=isnull(@sql+',','')+课程fromtbgroupby课程

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

selectm.*,n.总分,n.平均分

from

(select*fromtb pivot(max(分数)for课程in(语文,数学,物理))a)m,

(select姓名,sum(分数)总分,cast(avg(分数*1.0)asdecimal(18,2))平均分

fromtb

groupby姓名)n

wherem.姓名=n.姓名



4、使用SQL Server 2005动态SQL

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



--或使用isnull()

declare@sqlvarchar(8000)

select@sql=isnull(@sql+',','')+课程fromtbgroupby课程

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、建立表格

ifobject_id('tb')isnotnulldroptabletb

go

createtabletb(姓名varchar(10),语文int,数学int,物理int)

insertintotbvalues('张三',74,83,93)

insertintotbvalues('李四',74,84,94)

go

select*fromtb

go

姓名 语文 数学 物理

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

张三 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姓名,课程,分数fromtb unpivot (分数for课程in([语文],[数学],[物理])) t



4、使用SQL Server 2005动态SQL

--SQL SERVER 2005动态SQL

declare@sqlnvarchar(4000)

select@sql=isnull(@sql+',','')+quotename(Name)

fromsyscolumns

whereID=object_id('tb')andNamenotin('姓名')

orderbyColid



set@sql='select姓名,[课程],[分数] from tb unpivot ([分数] for [课程] in('+@sql+'))b'

exec(@sql)

读书人网 >PB

热点推荐

触屏版 | 电脑版

读书人网 m.reader8.net