数据库行列转置.docx
- 文档编号:23343458
- 上传时间:2023-05-16
- 格式:DOCX
- 页数:9
- 大小:16.39KB
数据库行列转置.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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 行列