復(fù)制代碼 代碼如下:
--行列互轉(zhuǎn)
/******************************************************************************************************************************************************
以學(xué)生成績?yōu)槔樱容^形象易懂
整理人:中國風(fēng)(Roy)
日期:2008.06.06
******************************************************************************************************************************************************/
--1、行互列
--> --> (Roy)生成測試數(shù)據(jù)
if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)
Insert Class
select N'張三',N'語文',78 union all
select N'張三',N'數(shù)學(xué)',87 union all
select N'張三',N'英語',82 union all
select N'張三',N'物理',90 union all
select N'李四',N'語文',65 union all
select N'李四',N'數(shù)學(xué)',77 union all
select N'李四',N'英語',65 union all
select N'李四',N'物理',85
Go
--2000方法:
動(dòng)態(tài):
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+' from Class group by [Student]')
生成靜態(tài):
select
[Student],
[數(shù)學(xué)]=max(case when [Course]='數(shù)學(xué)' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英語]=max(case when [Course]='英語' then [Score] else 0 end),
[語文]=max(case when [Course]='語文' then [Score] else 0 end)
from
Class
group by [Student]
GO
動(dòng)態(tài):
declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')
生成靜態(tài):
select *
from
Class
pivot
(max([Score]) for [Course] in([數(shù)學(xué)],[物理],[英語],[語文]))b
生成格式:
/*
Student 數(shù)學(xué) 物理 英語 語文
------- ----------- ----------- ----------- -----------
李四 77 85 65 65
張三 87 90 82 78
(2 行受影響)
*/
------------------------------------------------------------------------------------------
go
--加上總成績(學(xué)科平均分)
--2000方法:
動(dòng)態(tài):
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+',[總成績]=sum([Score]) from Class group by [Student]')--加多一列(學(xué)科平均分用avg([Score]))
生成動(dòng)態(tài):
select
[Student],
[數(shù)學(xué)]=max(case when [Course]='數(shù)學(xué)' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英語]=max(case when [Course]='英語' then [Score] else 0 end),
[語文]=max(case when [Course]='語文' then [Score] else 0 end),
[總成績]=sum([Score]) --加多一列(學(xué)科平均分用avg([Score]))
from
Class
group by [Student]
go
--2005方法:
動(dòng)態(tài):
declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一個(gè)逗號(hào)
exec('select [Student],'+@s+',[總成績] from (select *,[總成績]=sum([Score])over(partition by [Student]) from Class) a
pivot (max([Score]) for [Course] in('+@s+'))b ')
生成靜態(tài):
select
[Student],[數(shù)學(xué)],[物理],[英語],[語文],[總成績]
from
(select *,[總成績]=sum([Score])over(partition by [Student]) from Class) a --平均分時(shí)用avg([Score])
pivot
(max([Score]) for [Course] in([數(shù)學(xué)],[物理],[英語],[語文]))b
生成格式:
/*
Student 數(shù)學(xué) 物理 英語 語文 總成績
------- ----------- ----------- ----------- ----------- -----------
李四 77 85 65 65 292
張三 87 90 82 78 337
(2 行受影響)
*/
go
--2、列轉(zhuǎn)行
--> --> (Roy)生成測試數(shù)據(jù)
if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[數(shù)學(xué)] int,[物理] int,[英語] int,[語文] int)
Insert Class
select N'李四',77,85,65,65 union all
select N'張三',87,90,82,78
Go
--2000:
動(dòng)態(tài):
declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一個(gè)union all
+',[Score]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不轉(zhuǎn)換的列
order by Colid
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一個(gè)排序
生成靜態(tài):
select *
from (select [Student],[Course]='數(shù)學(xué)',[Score]=[數(shù)學(xué)] from Class union all
select [Student],[Course]='物理',[Score]=[物理] from Class union all
select [Student],[Course]='英語',[Score]=[英語] from Class union all
select [Student],[Course]='語文',[Score]=[語文] from Class)t
order by [Student],[Course]
go
--2005:
動(dòng)態(tài):
declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Class') and Name not in('Student')
order by Colid
exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')
go
select
Student,[Course],[Score]
from
Class
unpivot
([Score] for [Course] in([數(shù)學(xué)],[物理],[英語],[語文]))b
生成格式:
/*
Student Course Score
------- ------- -----------
李四 數(shù)學(xué) 77
李四 物理 85
李四 英語 65
李四 語文 65
張三 數(shù)學(xué) 87
張三 物理 90
張三 英語 82
張三 語文 78
(8 行受影響)
*/
您可能感興趣的文章:- SQL Server將一列的多行內(nèi)容拼接成一行的實(shí)現(xiàn)方法
- SQLServer行轉(zhuǎn)列實(shí)現(xiàn)思路記錄
- Sql Server 2000 行轉(zhuǎn)列的實(shí)現(xiàn)(橫排)
- sqlserver2005 行列轉(zhuǎn)換實(shí)現(xiàn)方法
- sqlserver下將數(shù)據(jù)庫記錄的列記錄轉(zhuǎn)換成行記錄的方法
- SQLServer行列互轉(zhuǎn)實(shí)現(xiàn)思路(聚合函數(shù))
- SQL Server行轉(zhuǎn)列的方法解析
- SQL Server基礎(chǔ)之行數(shù)據(jù)轉(zhuǎn)換為列數(shù)據(jù)