selectoutpatient_id,patient_name,abo_blood_group,rh_blood_group,sum("冷沉淀(u)"),sum("红细胞悬液(u)"),sum("血浆(袋)"),sum("血小板(机采)(ml)")from(selectinspection_id,group_id,outpatient_id,patient_name,abo_blood_group,rh_blood_group,--sum(caseblood_namewhen'冷沉淀'thenamountelse0end)as'1',--sum(caseblood_namewhen'红细胞悬液'thenamountelse0end)as'红细胞悬液',--sum(caseblood_namewhen'血浆'thenamountelse0end)as'血浆',max(decode(blood_name,'冷沉淀',amount,null))"冷沉淀(u)",sum(decode(blood_name,'红细胞悬液',amount,null))"红细胞悬液(u)",sum(decode(blood_name,'血浆',amount,null))"血浆(袋)",sum(decode(blood_name,'血小板(机采)',amount,null))"血小板(机采)(ml)"--blood_unitfrom(selectinspection_id,group_id,outpatient_id,patient_name,blood_name,abo_blood_group,rh_blood_group,sum(blood_amount)asamountfrom(selecta.inspection_id,a.group_id,a.outpatient_id,a.patient_name,c.blood_name,b.abo_blood_group,b.rh_blood_group,b.blood_amount,b.blood_unitfromlis_inspection_samplea,bis_blood_inputb,bis_blood_typecwherea.inspection_id=b.inspection_idandb.blood_type_id=c.blood_type_idanda.outpatient_id='0000319694'
anda.group_id='G013')groupbyinspection_id,group_id,outpatient_id,patient_name,blood_name,abo_blood_group,rh_blood_group)groupbyinspection_id,group_id,outpatient_id,patient_name,abo_blood_group,rh_blood_group)groupbyoutpatient_id,patient_name,abo_blood_group,rh_blood_group
select*from(selectinspection_date,count(inspection_id)asresultfromlis_inspection_samplewheregroup_id='G006'ANDinspection_datebetween'20090101'and'20091231'groupbyinspection_date)whereresult='2167'
selectsum(result)from(selectinspection_date,count(inspection_id)asresultfromlis_inspection_samplewhere--group_id='G017'andpatient_type='4'inspection_datebetween'20090101'and'20091231'groupbyinspection_date)
sql列转行
CREATETABLE[Test]([id][int]IDENTITY(1,1)NOTNULL,[name][nvarchar](50)COLLATEChinese_PRC_CI_ASNULL,[subject][nvarchar](50)COLLATEChinese_PRC_CI_ASNULL,[Source][numeric](18,0)NULL)ON[PRIMARY]GOINSERT',60)INSERT',70)INSERT',80)INSERT',75)INSERT',57)INSERT',80)INSERT',100)GoINTO[test]([name],[subject],[Source])values(N'张三',N'语文INTO[test]([name],[subject],[Source])values(N'李四',N'数学INTO[test]([name],[subject],[Source])values(N'王五',N'英语INTO[test]([name],[subject],[Source])values(N'王五',N'数学INTO[test]([name],[subject],[Source])values(N'王五',N'语文INTO[test]([name],[subject],[Source])values(N'李四',N'语文INTO[test]([name],[subject],[Source])values(N'张三',N'英语
--交叉表语句的实现:--用于:交叉表的列数是确定的selectname,sum(casesubjectwhen'数学'thensourceelse0end)as'数学',sum(casesubjectwhen'英语'thensourceelse0end)as'英语',sum(casesubjectwhen'语文'thensourceelse0end)as'语文'fromtestgroupbyname--用于:交叉表的列数是不确定的declare@sqlvarchar(8000)set@sql='selectname,'select@sql=@sql+'sum(casesubjectwhen'''+subject+'''thensourceelse0end)as'''+subject+''','from(selectdistinctsubjectfromtest)asaselect@sql=left(@sql,len(@sql)-1)+'fromtestgroupbyname'exec(@sql)go
--用于:交叉表的列数是不确定的declare@sqlvarchar(8000)set@sql='selectid,'select@sql=@sql+'(casesubjectwhen'''+subject+'''thensourceelsenullend)as'''+subject+''','from(selectdistinctsubjectfromtest)asaselect@sql=left(@sql,len(@sql)-1)+'fromtest'exec(@sql)go
SQL行列转换
2009-12-1016:12
问题:假设有张学生成绩表(tb)如下:
课程分数
张三语文74
张三数学83
张三物理93
李四语文74
李四数学84
李四物理94
想变成(得到如下结果):
语文数学物理
----------------
李四748494
张三748393
createtabletb(varchar(10),课程varchar(10),分数int)
insertintotbvalues('张三','语文',74)
insertintotbvalues('张三','数学',83)
insertintotbvalues('张三','物理',93)
insertintotbvalues('李四','语文',74)
insertintotbvalues('李四','数学',84)
insertintotbvalues('李四','物理',94)
go
--SQLSERVER2000静态SQL,指课程只有语文,数学,物理这三门课程.(以下同)
selectas,
max(case课程when'语文'then分数else0end)语文,
max(case课程when'数学'then分数else0end)数学,
max(case课程when'物理'then分数else0end)物理
fromtb
groupby
--SQLSERVER2000动态SQL,指课程不止语文,数学,物理这三门课程.(以下同)
declare@sqlvarchar(8000)
set@sql='select'
select@sql=@sql+',max(case课程when'''+课程+'''then分数else0end)['+课程+']'
from(selectdistinct课程fromtb)asa
set@sql=@sql+'fromtbgroupby'
exec(@sql)
--SQLSERVER2005静态SQL.
select*from(select*fromtb)apivot(max(分数)for课程in(语文,数学,物理))b
--SQLSERVER2005动态SQL.
declare@sqlvarchar(8000)
select@sql=isnull(@sql+'],[','')+课程fromtbgroupby课程
set@sql='['+@sql+']'
exec('select*from(select*fromtb)apivot(max(分数)for课程in('+@sql+'))b')
---------------------------------
/*
问题:在上述结果的基础上加平均分,总分,得到如下结果:
语文数学物理平均分总分
--------------------------
李四74849484.00252
张三74839383.33250
*/
--SQLSERVER2000静态SQL.
select,
max(case课程when'语文'then分数else0end)语文,
max(case课程when'数学'then分数else0end)数学,
max(case课程when'物理'then分数else0end)物理,
cast(avg(分数*1.0)asdecimal(18,2))平均分,
sum(分数)总分
fromtb
groupby
--SQLSERVER2000动态SQL.
declare@sqlvarchar(8000)
set@sql='select'
select@sql=@sql+',max(case课程when'''+课程+'''then分数else0end)['+课程+']'
from(selectdistinct课程fromtb)asa
set@sql=@sql+',cast(avg(分数*1.0)asdecimal(18,2))平均分,sum(分数)总分fromtbgroupby'
exec(@sql)
--SQLSERVER2005静态SQL.
selectm.*,n.平均分,n.总分from
(select*from(select*fromtb)apivot(max(分数)for课程in(语文,数学,物理))b)m,
(select,cast(avg(分数*1.0)asdecimal(18,2))平均分,sum(分数)总分fromtbgroupby)n
wherem.=n.
--SQLSERVER2005动态SQL.
declare@sqlvarchar(8000)
select@sql=isnull(@sql+',','')+课程fromtbgroupby课程
exec('selectm.*,n.平均分,n.总分from
(select*from(select*fromtb)apivot(max(分数)for课程in('+@sql+'))b)m,
(select,cast(avg(分数*1.0)asdecimal(18,2))平均分,sum(分数)总分fromtbgroupby)n
wherem.=n.')
droptabletb
------------------
------------------
/*
问题:如果上述两表互相换一下:即表结构和数据为:
语文数学物理
张三74
83
93
李四74
84
94
想变成(得到如下结果):
课程分数
------------
李四语文74
李四数学84
李四物理94
张三语文74
张三数学83
张三物理93
--------------
*/
createtabletb(varchar(10),语文int,数学int,物理int)
insertintotbvalues('张三',74,83,93)
insertintotbvalues('李四',74,84,94)
go
--SQLSERVER2000静态SQL.
select*from
(
select,课程='语文',分数=语文fromtb
unionall
select,课程='数学',分数=数学fromtb
unionall
select,课程='物理',分数=物理fromtb
)t
orderby,case课程when'语文'then1when'数学'then2when'物理'then3end
--SQLSERVER2000动态SQL.
--调用系统表动态生态.
declare@sqlvarchar(8000)
select@sql=isnull(@sql+'unionall','')+'select,[课程]='+quotename(Name,'''')+',[分数]='+quotename(Name)+'fromtb'
fromsyscolumns
wherename!=N''andID=object_id('tb')--表名tb,不包含列名为的其它列
orderbycolidasc
exec(@sql+'orderby')
--SQLSERVER2005动态SQL.
select,课程,分数fromtbunpivot(分数for课程in([语文],[数学],[物理]))t
--SQLSERVER2005动态SQL,同SQLSERVER2000动态SQL.
--------------------
/*
问题:在上述的结果上加个平均分,总分,得到如下结果:
课程分数
----------------
李四语文74.00
李四数学84.00
李四物理94.00
李四平均分84.00
李四总分252.00
张三语文74.00
张三数学83.00
张三物理93.00
张三平均分83.33
张三总分250.00
------------------
*/
select*from
(
selectas,课程='语文',分数=语文fromtb
unionall
selectas,课程='数学',分数=数学fromtb
unionall
selectas,课程='物理',分数=物理fromtb
unionall
selectas,课程='平均分',分数=cast((语文+数学+物理)*1.0/3asdecimal(18,2))fromtb
unionall
selectas,课程='总分',分数=语文+数学+物理fromtb
)t
orderby,case课程when'语文'then1when'数学'then2when'物理'then3when'平均分'then4when'总分'then5end
droptabletb
--------------------------------------------------
以下是dawugui回贴
--------------------------------------------------
createtableA(idchar(3),num1int,num2int,num3int,num4int)
insertAselect'001',80,90,50,60
insertAselect'002',84,70,60,82
go
--SQL2005实现方法:
select*fromA
unpivot
(numforcolin([num1],[num2],[num3],[num4]))T2)tmp
--SQL2000实现:
---调用系统表动态生态
declare@snvarchar(4000)
select
@s=isnull(@s+'
union
all
','')+'
select
ID,[num]='+quotename(Name,'''')+',Qty='+quotename(Name)+'fromA'
fromsyscolumns
whereName!=N'ID'andID=object_id('A')--表名A,不包含列名为ID的其它列
orderbycolidasc
exec(@s+'orderbyIDasc,[num]asc')
--生成的静态语句
selectID,[num]='num1',Qty=[num1]fromAunionall
selectID,[num]='num2',Qty=[num2]fromAunionall
selectID,[num]='num3',Qty=[num3]fromAunionall
selectID,[num]='num4',Qty=[num4]fromA
orderbyIDasc,[num]asc
/*
IDnumQty
-------------------
001num180
001num290
001num350
001num460
002num184
002num270
002num360
002num482
------------------------------
*/
--动态方法:
declare@snvarchar(4000)
select@s=isnull(@s+'unionall','')+'selectID,[num]='+quotename(Name)+'fromA'
fromsyscolumns
whereName!=N'ID'andID=object_id('A')
orderbycolidasc
exec(@s+'orderbyIDasc')
--生成的语句如下:
selectID,[num]=[num1]fromAunionall
selectID,[num]=[num2]fromAunionall
selectID,[num]=[num3]fromAunionall
selectID,[num]=[num4]fromA
orderbyIDasc,[num]asc
/*
IDnum
---------------
00180
00190
00150
00160
00282
00260
00270
00284
*/
---droptableA
/*
将表数据旋转90度(2007-11-19于海南三亚)
将下表数据:
A
b
c
d
e
----------------------------------------------------------------
x
1
2
3
4
y
5
6
7
8
z
9
10
11
12
转化成如下结果:
a
x
y
z
--------------------------------------------------
b
1
5
9
c
2
6
10
d
3
7
11
e
4
8
12
*/
--生成测试数据
createtabletest1(Avarchar(20),bint,cint,dint,eint)
insertintotest1select'x',1,2,3,4
insertintotest1select'y',5,6,7,8
insertintotest1select'z',9,10,11,12
go
--生成中间数据表
declare@svarchar(8000)
set@s='createtabletest2(avarchar(20)'
select@s=@s+','+A+'varchar(10)'fromtest1
set@s=@s+')'
exec(@s)
print@s
--借助中间表实现行列转换
declare@namevarchar(20)
declaret_cursorcursorfor
selectnamefromsyscolumns
whereid=object_id('test1')andcolid>1orderbycolid
opent_cursor
fetchnextfromt_cursorinto@name
while@@fetch_status=0
begin
exec('select'+@name+'astintotest3fromtest1')
set@s='insertintotest2select'''+@name+''''
select@s=@s+','''+rtrim(t)+''''fromtest3
exec(@s)
exec('droptabletest3')
fetchnextfromt_cursorinto@name
end
closet_cursor
deallocatet_cursor
--查看行列互换处理结果
select*fromtest1
select*fromtest2
--删除表
droptabletest1
droptabletest2
----------------------------------------------------------------------------
/*固定的写法:*/
selectt1.*,t2.y,t3.zfrom
(selecta='b',x=bfromtest1wherea='x')t1,
(selecta='b',y=bfromtest1wherea='y')t2,
(selecta='b',z=bfromtest1wherea='z')t3
wheret1.a=t2.aandt1.a=t2.a
unionall
selectt1.*,t2.y,t3.zfrom
(selecta='c',x=cfromtest1wherea='x')t1,
(selecta='c',y=cfromtest1wherea='y')t2,
(selecta='c',z=cfromtest1wherea='z')t3
wheret1.a=t2.aandt1.a=t2.a
unionall
selectt1.*,t2.y,t3.zfrom
(selecta='d',x=dfromtest1wherea='x')t1,
(selecta='d',y=dfromtest1wherea='y')t2,
(selecta='d',z=dfromtest1wherea='z')t3
wheret1.a=t2.aandt1.a=t2.a
unionall
selectt1.*,t2.y,t3.zfrom
(selecta='e',x=efromtest1wherea='x')t1,
(selecta='e',y=efromtest1wherea='y')t2,
(selecta='e',z=efromtest1wherea='z')t3
wheret1.a=t2.aandt1.a=t2.a
----------------------------------------------------------------------------
/*
表tb,数据如下:
项目种类业绩提成
洗吹类
20010
外卖
1005
合计
30015
转换成:
项目种类洗吹类外卖合计
业绩
200
100300
提成
10
5
15
*/
createtabletb
(
项目种类varchar(10),
业绩
int,
提成
int
)
insertintotb(项目种类,业绩,提成)values('洗吹类',200,10)
insertintotb(项目种类,业绩,提成)values('外卖',100,5)
insertintotb(项目种类,业绩,提成)values('合计',300,15)
go
select项目种类,sum(洗吹类)as洗吹类,sum(外卖)as外卖,sum(合计)as合计from
(
select项目种类='业绩',
洗吹类=casewhen项目种类='洗吹类'then业绩else0end,
外卖
=casewhen项目种类='外卖'then业绩else0end,
合计
=casewhen项目种类='合计'then业绩else0end
fromtb
unionall
select项目种类='提成',
洗吹类=casewhen项目种类='洗吹类'then提成else0end,
外卖
=casewhen项目种类='外卖'then提成else0end,
合计
=casewhen项目种类='合计'then提成else0end
fromtb
)m
groupby项目种类
orderby项目种类desc
droptabletb
/*
项目种类洗吹类
外卖
合计
-----------------------------------------
业绩
200
100
300
提成
10
5
15
(所影响的行数为2行)
*/
--------------------------------------------------------------------------
/*
数据库中tb表格如下
月份
工资福利奖金
1月
100
200300
2月
110
210310
3月
120
220320
4月
130
230330
我想得到的结果是
项目1月
2月3月4月
工资100
110120130
福利200
210220230
奖金300
310320330
就是说完全把表格的行列颠倒,有点像那种旋转矩阵,请问如何用sql语句实现?
*/
ifexists(select*fromdbo.sysobjects
whereid=object_id(N'[dbo].[p_zj]')andOBJECTPROPERTY(id,N'IsProcedure')=1)
dropprocedure[dbo].[p_zj]
GO
/*--行列互换的通用存储过程(原著:邹建):将指定的表,按指定的字段进行行列互换*/
createprocp_zj
@tbnamesysname,--要处理的表名
@fdnamesysname,--做为转换的列名
@new_fdnamesysname=''--为转换后的列指定列名
as
declare@s1varchar(8000),@s2varchar(8000),
@s3varchar(8000),@s4varchar(8000),
@s5varchar(8000),@ivarchar(10)
select@s1='',@s2='',@s3='',@s4='',@s5='',@i='0'
select@s1=@s1+',@'+@i+'varchar(8000)',
@s2=@s2+',@'+@i+'='''+caseisnull(@new_fdname,'')when''then''
else@new_fdname+'='end+''''''+name+'''''''',
@s3=@s3+'select@'+@i+'=@'+@i+'+'',[''+['+@fdname+
']+'']=''+cast(['+name+']asvarchar)from['+@tbname+']',
@s4=@s4+',@'+@i+'=''select''+@'+@i,
@s5=@s5+'+''unionall''+@'+@i,
@i=cast(@iasint)+1
fromsyscolumns
whereobject_id(@tbname)=idandname<>@fdname
select@s1=substring(@s1,2,8000),
@s2=substring(@s2,2,8000),
@s4=substring(@s4,2,8000),
@s5=substring(@s5,16,8000)
exec('declare'+@s1+'select'+@s2+@s3+'select'+@s4+'
exec('+@s5+')')
go
--用上面的存储过程测试:
createtableTest(月份varchar(4),工资int,福利int,奖金int)
insertTest
select'1月',100,200,300unionall
select'2月',110,210,310unionall
select'3月',120,220,320unionall
select'4月',130,230,330
go
execp_zj'Test','月份','项目'
droptableTest
dropprocp_zj
/*
项目1月
2月
3月
4月
------------------------------------------------
福利200
210
220
230
工资100
110
120
130
奖金300
310
320
330
(所影响的行数为3行)
*/
/*
静态写法(SQL2005)
*/
--测试环境
createtableTest(月份varchar(4),工资int,福利int,奖金int)
insertTest
select'1月',100,200,300unionall
select'2月',110,210,310unionall
select'3月',120,220,320unionall
select'4月',130,230,330
go
--测试语句
SELECT*FROM
(
SELECT考核月份,月份,金额FROM
(SELECT月份,工资,福利,奖金FROMTest)p
UNPIVOT
(金额FOR考核月份IN(工资,福利,奖金))ASunpvt
)T
PIVOT
(MAX(金额)FOR月份in([1月],[2月],[3月],[4月]))ASpt
--测试结果
/*
考核月份1月
2月
3月
4月
------------------------------
福利200210220230
工资100110120130
奖金300310320330
*/
--删除环境
DroptableTest
sql行列转换
2008-11-1417:18
普通行列转换
假设有张学生成绩表(tb)如下:NameSubjectResult张三语文张三数学张三物理李四语文李四数学李四物理想变成语文数学物理748393748494
------------------------------------------李四张三747484839493
createtabletb(Namevarchar(10),
Subjectvarchar(10),Resultint)
insertintotb(Name,Subject,Result)values('张三','语文',74)insertintotb(Name,Subject,Result)values('张三','数学',83)insertintotb(Name,Subject,Result)values('张三','物理',93)insertintotb(Name,Subject,Result)values('李四','语文',74)insertintotb(Name,Subject,Result)values('李四','数学',84)insertintotb(Name,Subject,Result)values('李四','物理',94)go
--静态SQL,指subject只有语文,数学,物理这三门课程.selectname,max(casesubjectwhen'语文'thenresultelse0end)语文,max(casesubjectwhen'数学'thenresultelse0end)数学,max(casesubjectwhen'物理'thenresultelse0end)物理fromtbgroupbyname
语文
数学
物理
------------------------------------------李四张三747484839493
--动态SQL,指subject不止语文,数学,物理这三门课程.declare@sqlvarchar(8000)set@sql='selectNameas'+''select@sql=@sql+',max(caseSubjectwhen'''+Subject+'''thenResultelse0end)['+Subject+']'from(selectdistinctSubjectfromtb)asaset@sql=@sql+'fromtbgroupbyname'exec(@sql)
数学
物理
语文
------------------------------------------李四张三848394937474
------------------------------------------------------------------加个平均分,总分
语文
数学
物理
平均分
总分
-------------------------------------------------------------------------李四张三74748483949384.0083.33252250
--静态SQL,指subject只有语文,数学,物理这三门课程.selectname,max(casesubjectwhen'语文'thenresultelse0end)语文,max(casesubjectwhen'数学'thenresultelse0end)数学,max(casesubjectwhen'物理'thenresultelse0end)物理,cast(avg(result*1.0)asdecimal(18,2))平均分,sum(result)总分fromtbgroupbyname语文数学物理平均分总分
-------------------------------------------------------------------------李四张三74748483949384.0083.33252250
--动态SQL,指subject不止语文,数学,物理这三门课程.declare@sql1varchar(8000)set@sql1='selectNameas'+''select@sql1=@sql1+',max(caseSubjectwhen'''+Subject+'''thenResultelse0end)['+Subject+']'from(selectdistinctSubjectfromtb)asaset@sql1=@sql1+',cast(avg(result*1.0)asdecimal(18,2))平均分,sum(result)总分fromtbgroupbyname'exec(@sql1)
数学
物理
语文
平均分
总分
-------------------------------------------------------------------------李四张三84839493747484.0083.33252250
droptabletb
--------------------------------------------------------如果上述两表互相换一下:即
语文数学物理张三74李四7483849394
想变成NameSubjectResult
---------------------------李四李四李四张三张三张三语文数学物理语文数学物理748494748393
createtabletb1(varchar(10),语文int,数学int,物理int)
insertintotb1(,语文,数学,物理)values('张三',74,83,93)insertintotb1(,语文,数学,物理)values('李四',74,84,94)
select*from(selectasName,Subject='语文',Result=语文fromtb1unionallselectasName,Subject='数学',Result=数学fromtb1unionallselectasName,Subject='物理',Result=物理fromtb1)torderbyname,caseSubjectwhen'语文'then1when'数学'then2when'物理'then3when'总分'then4end
-------------------------------------------------------------------加个平均分,总分NameSubjectResult-------------------74.0084.0094.0084.00252.0074.0083.0093.0083.33250.00
----------------李四李四李四李四李四张三张三张三张三张三语文数学物理平均分总分语文数学物理平均分总分
select*from
(selectasName,Subject='语文',Result=语文fromtb1unionallselectasName,Subject='数学',Result=数学fromtb1unionallselectasName,Subject='物理',Result=物理fromtb1unionallselectasName,Subject='平均分',Result=cast((语文+数学+物理)*1.0/3asdecimal(18,2))fromtb1unionallselectasName,Subject='总分',Result=语文+数学+物理fromtb1)torderbyname,caseSubjectwhen'语文'then1when'数学'then2when'物理'then3when'平均分'then4when'总分'then5end
droptabletb1
[行业交流]
