数据库行列转置.docx
《数据库行列转置.docx》由会员分享，可在线阅读，更多相关《数据库行列转置.docx（9页珍藏版）》请在冰点文库上搜索。
数据库行列转置
SQLcode
/*
普通行列转换
（爱新觉罗.毓华2007-11-18于海南三亚）
假设有张学生成绩表（tb）如下:
NameSubjectResult
张三语文 74
张三数学 83
张三物理 93
李四语文 74
李四数学 84
李四物理 94
*/
-------------------------------------------------------------------------
/*
想变成
姓名语文数学物理
-------------------------------------------
李四748494
张三748393
*/
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）物理
fromtb
groupbyname
/*
姓名语文数学物理
-------------------------------------------
李四748494
张三748393
*/
--动态SQL,指subject不止语文、数学、物理这三门课程。
declare@sqlvarchar（8000）
set@sql='selectNameas'+'姓名'
select@sql=@sql+',max（caseSubjectwhen'''+Subject+'''thenResultelse0end）['+Subject+']'
from（selectdistinctSubjectfromtb）asa
set@sql=@sql+'fromtbgroupbyname'
exec（@sql）
/*
姓名数学物理语文
-------------------------------------------
李四849474
张三839374
*/
-------------------------------------------------------------------
/*加个平均分，总分
姓名语文数学物理平均分总分
--------------------------------------------------------------------------
李四74849484.00252
张三74839383.33250
*/
--静态SQL,指subject只有语文、数学、物理这三门课程。
selectname姓名,
max（casesubjectwhen'语文'thenresultelse0end）语文,
max（casesubjectwhen'数学'thenresultelse0end）数学,
max（casesubjectwhen'物理'thenresultelse0end）物理,
cast（avg（result*1.0）asdecimal（18,2））平均分,
sum（result）总分
fromtb
groupbyname
/*
姓名语文数学物理平均分总分
--------------------------------------------------------------------------
李四74849484.00252
张三74839383.33250
*/
--动态SQL,指subject不止语文、数学、物理这三门课程。
declare@sql1varchar（8000）
set@sql1='selectNameas'+'姓名'
select@sql1=@sql1+',max（caseSubjectwhen'''+Subject+'''thenResultelse0end）['+Subject+']'
from（selectdistinctSubjectfromtb）asa
set@sql1=@sql1+',cast（avg（result*1.0）asdecimal（18,2））平均分,sum（result）总分fromtbgroupbyname'
exec（@sql1）
/*
姓名数学物理语文平均分总分
--------------------------------------------------------------------------
李四84947484.00252
张三83937483.33250
*/
droptabletb
---------------------------------------------------------
---------------------------------------------------------
/*
如果上述两表互相换一下：
即
姓名语文数学物理
张三74 83 93
李四74 84 94
想变成
NameSubjectResult
----------------------------
李四语文74
李四数学84
李四物理94
张三语文74
张三数学83
张三物理93
*/
createtabletb1
（
姓名varchar（10）,
语文int,
数学int,
物理int
）
insertintotb1（姓名,语文,数学,物理）values（'张三',74,83,93）
insertintotb1（姓名,语文,数学,物理）values（'李四',74,84,94）
select*from
（
select姓名asName,Subject='语文',Result=语文fromtb1
unionall
select姓名asName,Subject='数学',Result=数学fromtb1
unionall
select姓名asName,Subject='物理',Result=物理fromtb1
）t
orderbyname,caseSubjectwhen'语文'then1when'数学'then2when'物理'then3when'总分'then4end
--------------------------------------------------------------------
/*加个平均分,总分
NameSubjectResult
-------------------------------------
李四语文74.00
李四数学84.00
李四物理94.00
李四平均分84.00
李四总分252.00
张三语文74.00
张三数学83.00
张三物理93.00
张三平均分83.33
张三总分250.00
*/
select*from
（
select姓名asName,Subject='语文',Result=语文fromtb1
unionall
select姓名asName,Subject='数学',Result=数学fromtb1
unionall
select姓名asName,Subject='物理',Result=物理fromtb1
unionall
select姓名asName,Subject='平均分',Result=cast（（语文+数学+物理）*1.0/3asdecimal（18,2））fromtb1
unionall
select姓名asName,Subject='总分',Result=语文+数学+物理fromtb1
）t
orderbyname,caseSubjectwhen'语文'then1when'数学'then2when'物理'then3when'平均分'then4when'总分'then5end
droptabletb1
-->2005静态
select*from（select*from#T）apivot（max（分数）for课程in（语文,数学,英语））b
-->2005动态
declare@2005nvarchar（4000）
select@2005=isnull（@2005+',',''）+课程from#Tgroupby课程
exec（'select*from（select*from#T）apivot（max（分数）for课程in（'+@2005+'））b'）
SELECT
（casewhena.colorder=1thend.nameelse''end）表名,
a.colorder字段序号,
a.name字段名,
（casewhenCOLUMNPROPERTY（a.id,a.name,'IsIdentity'）=1then'√'else''end）标识,
（casewhen（SELECTcount（*）
FROMsysobjects
WHERE（namein
（SELECTname
FROMsysindexes
WHERE（id=a.id）AND（indidin
（SELECTindid
FROMsysindexkeys
WHERE（id=a.id）AND（colidin
（SELECTcolid
FROMsyscolumns
WHERE（id=a.id）AND（name=a.name）））））））AND
（xtype='PK'））>0then'√'else''end）主键,
b.name类型,
a.length占用字节数,
COLUMNPROPERTY（a.id,a.name,'PRECISION'）as长度,
isnull（COLUMNPROPERTY（a.id,a.name,'Scale'）,0）as小数位数,
（casewhena.isnullable=1then'√'else''end）允许空,
isnull（e.text,''）默认值,
isnull（g.[value],''）AS字段说明
FROMsyscolumnsaleftjoinsystypesb
ona.xtype=b.xusertype
innerjoinsysobjectsd
ona.id=d.idandd.xtype='U'andd.name<>'dtproperties'
leftjoinsyscommentse
ona.cdefault=e.id
leftjoinsyspropertiesg
ona.id=g.idANDa.colid=g.smallid
orderbya.id,a.colorder
升级会员 