*
标题:普通行列转换(version
2.0
)
作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间:
2008
-
03
-
09
地点:广东深圳
说明:普通行列转换(version
1.0
)仅针对sql server 2000提供静态和动态写法,version
2
.0增加sql server 2005的有关写法。
问题:假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文
74
张三 数学
83
张三 物理
93
李四 语文
74
李四 数学
84
李四 物理
94
想变成(得到如下结果):
姓名 语文 数学 物理
--
-- ---- ---- ----
李四
74
84
94
张三
74
83
93
--
-----------------
*/
create
table
tb(姓名
varchar
(
10
) , 课程
varchar
(
10
) , 分数
int
)
insert
into
tb
values
(
'
张三
'
,
'
语文
'
,
74
)
insert
into
tb
values
(
'
张三
'
,
'
数学
'
,
83
)
insert
into
tb
values
(
'
张三
'
,
'
物理
'
,
93
)
insert
into
tb
values
(
'
李四
'
,
'
语文
'
,
74
)
insert
into
tb
values
(
'
李四
'
,
'
数学
'
,
84
)
insert
into
tb
values
(
'
李四
'
,
'
物理
'
,
94
)
go
--
SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select
姓名
as
姓名 ,
max
(
case
课程
when
'
语文
'
then
分数
else
0
end
) 语文,
max
(
case
课程
when
'
数学
'
then
分数
else
0
end
) 数学,
max
(
case
课程
when
'
物理
'
then
分数
else
0
end
) 物理
from
tb
group
by
姓名
--
SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare
@sql
varchar
(
8000
)
set
@sql
=
'
select 姓名
'
select
@sql
=
@sql
+
'
, max(case 课程 when
'''
+
课程
+
'''
then 分数 else 0 end) [
'
+
课程
+
'
]
'
from
(
select
distinct
课程
from
tb)
as
a
set
@sql
=
@sql
+
'
from tb group by 姓名
'
exec
(
@sql
)
--
SQL SERVER 2005 静态SQL。
select
*
from
(
select
*
from
tb) a pivot (
max
(分数)
for
课程
in
(语文,数学,物理)) b
--
SQL SERVER 2005 动态SQL。
declare
@sql
varchar
(
8000
)
select
@sql
=
isnull
(
@sql
+
'
],[
'
,
''
)
+
课程
from
tb
group
by
课程
set
@sql
=
'
[
'
+
@sql
+
'
]
'
exec
(
'
select * from (select * from tb) a pivot (max(分数) for 课程 in (
'
+
@sql
+
'
)) b
'
)
--
-------------------------------
/*
问题:在上述结果的基础上加平均分,总分,得到如下结果:
姓名 语文 数学 物理 平均分 总分
---- ---- ---- ---- ------ ----
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/
--
SQL SERVER 2000 静态SQL。
select
姓名 姓名,
max
(
case
课程
when
'
语文
'
then
分数
else
0
end
) 语文,
max
(
case
课程
when
'
数学
'
then
分数
else
0
end
) 数学,
max
(
case
课程
when
'
物理
'
then
分数
else
0
end
) 物理,
cast
(
avg
(分数
*
1.0
)
as
decimal
(
18
,
2
)) 平均分,
sum
(分数) 总分
from
tb
group
by
姓名
--
SQL SERVER 2000 动态SQL。
declare
@sql
varchar
(
8000
)
set
@sql
=
'
select 姓名
'
select
@sql
=
@sql
+
'
, max(case 课程 when
'''
+
课程
+
'''
then 分数 else 0 end) [
'
+
课程
+
'
]
'
from
(
select
distinct
课程
from
tb)
as
a
set
@sql
=
@sql
+
'
, cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名
'
exec
(
@sql
)
--
SQL SERVER 2005 静态SQL。
select
m.
*
, n.平均分 , n.总分
from
(
select
*
from
(
select
*
from
tb) a pivot (
max
(分数)
for
课程
in
(语文,数学,物理)) b) m,
(
select
姓名 ,
cast
(
avg
(分数
*
1.0
)
as
decimal
(
18
,
2
)) 平均分 ,
sum
(分数) 总分
from
tb
group
by
姓名) n
where
m.姓名
=
n.姓名
--
SQL SERVER 2005 动态SQL。
declare
@sql
varchar
(
8000
)
select
@sql
=
isnull
(
@sql
+
'
,
'
,
''
)
+
课程
from
tb
group
by
课程
exec
(
'
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (
'
+
@sql
+
'
)) b) m ,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名
'
)
drop
table
tb
转载于:https://www.cnblogs.com/Tonyyang/archive/2009/10/06/1578397.html