/*
普通行列轉換
(愛新覺羅.毓華?2007-11-18于海南三亞)

假設有張學生成績表(tb)如下:
Name?Subject?Result
張三?語文　　74
張三?數(shù)學　　83
張三?物理　　93
李四?語文　　74
李四?數(shù)學　　84
李四?物理　　94
*/

-------------------------------------------------------------------------
/*
想變成?
姓名?????????語文????????數(shù)學????????物理??????????
----------?-----------?-----------?-----------?
李四?????????74??????????84??????????94
張三?????????74??????????83??????????93
*/

create?table?tb
(
???Name????varchar(10)?,
???Subject?varchar(10)?,
???Result??int
)

insert?into?tb(Name?,?Subject?,?Result)?values('張三'?,?'語文'?,?74)
insert?into?tb(Name?,?Subject?,?Result)?values('張三'?,?'數(shù)學'?,?83)
insert?into?tb(Name?,?Subject?,?Result)?values('張三'?,?'物理'?,?93)
insert?into?tb(Name?,?Subject?,?Result)?values('李四'?,?'語文'?,?74)
insert?into?tb(Name?,?Subject?,?Result)?values('李四'?,?'數(shù)學'?,?84)
insert?into?tb(Name?,?Subject?,?Result)?values('李四'?,?'物理'?,?94)
go

--靜態(tài)SQL,指subject只有語文、數(shù)學、物理這三門課程。
select?name?姓名,
??max(case?subject?when?'語文'?then?result?else?0?end)?語文,
??max(case?subject?when?'數(shù)學'?then?result?else?0?end)?數(shù)學,
??max(case?subject?when?'物理'?then?result?else?0?end)?物理
from?tb
group?by?name
/*
姓名?????????語文????????數(shù)學????????物理??????????
----------?-----------?-----------?-----------?
李四?????????74??????????84??????????94
張三?????????74??????????83??????????93
*/

--動態(tài)SQL,指subject不止語文、數(shù)學、物理這三門課程。
declare?@sql?varchar(8000)
set?@sql?=?'select?Name?as?'?+?'姓名'
select?@sql?=?@sql?+?'?,?max(case?Subject?when?'''?+?Subject?+?'''?then?Result?else?0?end)?['?+?Subject?+?']'
from?(select?distinct?Subject?from?tb)?as?a
set?@sql?=?@sql?+?'?from?tb?group?by?name'
exec(@sql)?
/*
姓名?????????數(shù)學????????物理????????語文??????????
----------?-----------?-----------?-----------?
李四?????????84??????????94??????????74
張三?????????83??????????93??????????74
*/

-------------------------------------------------------------------
/*加個平均分，總分
姓名?????????語文????????數(shù)學????????物理????????平均分????????????????總分??????????
----------?-----------?-----------?-----------?--------------------?-----------?
李四?????????74??????????84??????????94??????????84.00????????????????252
張三?????????74??????????83??????????93??????????83.33????????????????250
*/

--靜態(tài)SQL,指subject只有語文、數(shù)學、物理這三門課程。
select?name?姓名,
??max(case?subject?when?'語文'?then?result?else?0?end)?語文,
??max(case?subject?when?'數(shù)學'?then?result?else?0?end)?數(shù)學,
??max(case?subject?when?'物理'?then?result?else?0?end)?物理,
??cast(avg(result*1.0)?as?decimal(18,2))?平均分,
??sum(result)?總分
from?tb
group?by?name
/*
姓名?????????語文????????數(shù)學????????物理????????平均分????????????????總分??????????
----------?-----------?-----------?-----------?--------------------?-----------?
李四?????????74??????????84??????????94??????????84.00????????????????252
張三?????????74??????????83??????????93??????????83.33????????????????250
*/

--動態(tài)SQL,指subject不止語文、數(shù)學、物理這三門課程。
declare?@sql1?varchar(8000)
set?@sql1?=?'select?Name?as?'?+?'姓名'
select?@sql1?=?@sql1?+?'?,?max(case?Subject?when?'''?+?Subject?+?'''?then?Result?else?0?end)?['?+?Subject?+?']'
from?(select?distinct?Subject?from?tb)?as?a
set?@sql1?=?@sql1?+?'?,?cast(avg(result*1.0)?as?decimal(18,2))?平均分,sum(result)?總分?from?tb?group?by?name'
exec(@sql1)?
/*
姓名?????????數(shù)學????????物理????????語文????????平均分????????????????總分??????????
----------?-----------?-----------?-----------?--------------------?-----------?
李四?????????84??????????94??????????74??????????84.00????????????????252
張三?????????83??????????93??????????74??????????83.33????????????????250
*/

drop?table?tb????

---------------------------------------------------------
---------------------------------------------------------
/*
如果上述兩表互相換一下：即

姓名?語文?數(shù)學?物理
張三?74　　83　　93
李四?74　　84　　94

想變成?
Name???????Subject?Result??????
----------?-------?-----------?
李四?????????語文??????74
李四?????????數(shù)學??????84
李四?????????物理??????94
張三?????????語文??????74
張三?????????數(shù)學??????83
張三?????????物理??????93
*/

create?table?tb1
(
???姓名?varchar(10)?,
???語文?int?,
???數(shù)學?int?,
???物理?int
)

insert?into?tb1(姓名?,?語文?,?數(shù)學?,?物理)?values('張三',74,83,93)
insert?into?tb1(姓名?,?語文?,?數(shù)學?,?物理)?values('李四',74,84,94)

select?*?from
(
??select?姓名?as?Name?,?Subject?=?'語文'?,?Result?=?語文?from?tb1?
??union?all
??select?姓名?as?Name?,?Subject?=?'數(shù)學'?,?Result?=?數(shù)學?from?tb1
??union?all
??select?姓名?as?Name?,?Subject?=?'物理'?,?Result?=?物理?from?tb1
)?t
order?by?name?,?case?Subject?when?'語文'?then?1?when?'數(shù)學'?then?2?when?'物理'?then?3?when?'總分'?then?4?end

--------------------------------------------------------------------
/*加個平均分,總分
Name???????Subject?????Result???????????????
----------?-------????--------------------?
李四?????????語文??????74.00
李四?????????數(shù)學??????84.00
李四?????????物理??????94.00
李四?????????平均分????84.00
李四?????????總分??????252.00
張三?????????語文??????74.00
張三?????????數(shù)學??????83.00
張三?????????物理??????93.00
張三?????????平均分????83.33
張三?????????總分??????250.00
*/

select?*?from
(
??select?姓名?as?Name?,?Subject?=?'語文'?,?Result?=?語文?from?tb1?
??union?all
??select?姓名?as?Name?,?Subject?=?'數(shù)學'?,?Result?=?數(shù)學?from?tb1
??union?all
??select?姓名?as?Name?,?Subject?=?'物理'?,?Result?=?物理?from?tb1
??union?all
??select?姓名?as?Name?,?Subject?=?'平均分'?,?Result?=?cast((語文?+?數(shù)學?+?物理)*1.0/3?as?decimal(18,2))?from?tb1
??union?all
??select?姓名?as?Name?,?Subject?=?'總分'?,?Result?=?語文?+?數(shù)學?+?物理?from?tb1
)?t
order?by?name?,?case?Subject?when?'語文'?then?1?when?'數(shù)學'?then?2?when?'物理'?then?3?when?'平均分'?then?4?when?'總分'?then?5?end

drop?table?tb1 ajiaoch

轉載于:https://www.cnblogs.com/babyt/archive/2008/10/21/1315972.html

總結

以上是生活随笔為你收集整理的[转]SQL交叉表实例的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。