--行列互转
/******************************************************************************************************************************************************
以学生成绩为例子，比较形象易懂
整理人：中国风(Roy)
日期:2008.06.06
******************************************************************************************************************************************************/
--1、行互列
--> --> (Roy)生成測試數據
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'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85
Go
--2000方法:
动态:
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]')
生成静态:
select
[Student],
[数学]=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),
[语文]=max(case when [Course]='语文' then [Score] else 0 end)
from
Class
group by [Student]
GO
动态:
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')
生成静态:
select *
from
Class
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b
生成格式:
/*
Student 数学 物理 英语 语文
------- ----------- ----------- ----------- -----------
李四 77 85 65 65
张三 87 90 82 78
(2 行受影响)
*/
------------------------------------------------------------------------------------------
go
--加上总成绩(学科平均分)
--2000方法:
动态:
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]')--加多一列(学科平均分用avg([Score]))
生成动态:
select
[Student],
[数学]=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),
[语文]=max(case when [Course]='语文' then [Score] else 0 end),
[总成绩]=sum([Score]) --加多一列(学科平均分用avg([Score]))
from
Class
group by [Student]
go
--2005方法:
动态:
declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一个逗号
exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a
pivot (max([Score]) for [Course] in('+@s+'))b ')
生成静态:
select
[Student],[数学],[物理],[英语],[语文],[总成绩]
from
(select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b
生成格式:
/*
Student 数学 物理 英语 语文 总成绩
------- ----------- ----------- ----------- ----------- -----------
李四 77 85 65 65 292
张三 87 90 82 78 337
(2 行受影响)
*/
go
--2、列转行
--> --> (Roy)生成測試數據
if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)
Insert Class
select N'李四',77,85,65,65 union all
select N'张三',87,90,82,78
Go
--2000:
动态:
declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[Score]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列
order by Colid
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序
生成静态:
select *
from (select [Student],[Course]='数学',[Score]=[数学] 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:
动态:
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([数学],[物理],[英语],[语文]))b
生成格式:
/*
Student Course Score
------- ------- -----------
李四 数学 77
李四 物理 85
李四 英语 65
李四 语文 65
张三 数学 87
张三 物理 90
张三 英语 82
张三 语文 78
(8 行受影响)
*/
ALTER procedure dbo.CommonRowToCol
@sql nvarchar(4000)
as
begin
--必须包含colname列和result列(不区分大小写),
--除colname列和result列 其余各列均会作为判别记录唯一性的条件
--使用时,只需将要作为列名的字段定义为colname,将欲显示的列定义为result即可
--如select student as 学生 ,course as colname,score result from class
create table #temp([序号] int IDENTITY(1,1))
declare @sqlTemp nvarchar(4000),@sql_select nvarchar(1000),@temp nvarchar(500)
declare @sql_All nvarchar(4000),@sql_Table nvarchar(1000),@sql_declare nvarchar(1000),@sql_Fetch nvarchar(1000) ,
@sql_id nvarchar(1000),@sql_id2 nvarchar(1000),@sql_insert nvarchar(1000),@sql_value nvarchar(1000)
set @sql_select = '' set @sql_All = '' set @sql_Table ='' set @sql_declare = '' set @sql_Fetch = ''
set @sql_id = '' set @sql_id2 = '' set @sql_insert='' set @sql_value = ''
set @sql = lower(LTRIM(@sql))
set @sqlTemp = @sql
set @sqlTemp = ' select top 0 * into #tempTbl from ('+@sqlTemp+')z '+
' select @temp = Name+'',''+@temp from ( select top 1111 name from tempdb..syscolumns where id = object_id(N''tempdb..#tempTbl'') order by colorder )z '
EXECUTE sp_executesql @sqlTemp, N'@temp nvarchar(1000) output',@sql_select output
while charindex(',',@sql_select) > 0
begin
select @temp = lower(LEFT(@sql_select,charindex(',',@sql_select)-1))
select @sql_select = stuff(@sql_select,1,charindex(',',@sql_select),'')
if (@temp <> 'colname') and (@temp<>'result')
begin
set @sql_Table = ' ['+@temp+'] nvarchar(1000),' + @sql_Table
set @sql_insert = ' ['+@temp+'],' + @sql_insert
set @sql_value ='isnull(@'+@temp+',''''),'+@sql_value
set @sql_id = ' ['+@temp+'] = isnull(@'+@temp+','''') and' + @sql_id
set @sql_id2 = ' ['+@temp+'] = isnull(''''''+@'+@temp+'+'''''' ,'''''''') and' + @sql_id2
end;
set @sql_declare = ' @'+@temp+' nvarchar(1000),'+@sql_declare
set @sql_Fetch = ' @'+@temp+','+@sql_Fetch
end;
set @sql_Table = 'ALTER TABLE #temp ADD ' +left(@sql_Table,len(@sql_table)-1)+' '
set @sql_declare = 'declare @sqlTemp nvarchar(4000),' +left(@sql_declare,len(@sql_declare)-1) + ' '
set @sql_Fetch = left(@sql_Fetch,len(@sql_fetch)-1) + ' '
set @sql_id = left(@sql_id,len(@sql_id)-3) + ' '
set @sql_id2 = left(@sql_id2,len(@sql_id2)-3) + ' '
set @sql_insert = left(@sql_insert,len(@sql_insert)-1) + ' '
set @sql_value = left(@sql_value,len(@sql_value)-1) + ' '
set @sql_All = @sql_declare +
' Declare myCur Cursor For '+ @sql + ' Open myCur Fetch NEXT From myCur Into '+ @sql_Fetch+
' While @@fetch_status=0 Begin '+
' if not exists(select * from tempdb..syscolumns where id = object_id(N''tempdb..#temp'') and name = @colName)
begin
set @sqlTemp =''alter table #temp add [''+@colName+''] nvarchar(4000) ''
exec(@sqlTemp)
end
if not exists(select * from #temp where '+@sql_id+')
begin
insert into #temp('+@sql_insert+') values('+@sql_value+')
end
set @sqlTemp ='' update #temp set [''+@colName+''] = isnull(''''''+@result+'''''','''''''') where ' + @sql_id2+'''
exec(@sqlTemp)
Fetch NEXT From myCur Into '+ @sql_Fetch+
'end
Close myCur
Deallocate myCur
select * from #temp
'
exec (@sql_Table)
exec(@sql_All)
end
-- SQL Server 2005实现动态交叉表存储过程
--动态交叉表就是列表会根据表中数据的情况动态创建列。
create procedure corss
@strTabName varchar(50), --表名
@strCol varchar(50), --列名
@strGroup varchar(50), --分组字段
@strNumber varchar(50), --被统计的字段
@strSum varchar(10)='Sum' --运算方式
as
declare @strSql varchar(1000),@strTempCol varchar(100)
execute('declare corss_cursor for select distinct'+@strCol+'from'+@strTabName+'for read only') --生成游标
begin
set nocount on
set @strSql='select'+@strGroup+','+@strSum+'('+@strNumber+') as ['+@strNumber+']' --查询的前半段
open corss_cursor
while(0=0)
begin
fetch next from corss_cursor --遍历游标，将列头信息放入变量@strTempCol
into @strTempCol
if(@@fetch_status<>0)break
set @strSql=@strSql+','+@strSum+'(case'+@strCol+'when'''+@strTempCol+'''then'+@strNumber+'else null end)as ['+@strTempCol+']'--gz查询
end
set @strSql=@strSql+'from'+@strTabName+'group by'+@strGroup --构造查询
execute(@strSql)
if @@error<>0 return @@error --如果出错，返回错误代码
close corss_cursor
deallocate corss_cursor return 0 --释放游标，返回0表示成功
end