SqlServer 2000/2005 列转行 行转列收集

--行列互转
/******************************************************************************************************************************************************
以学生成绩为例子，比较形象易懂

整理人：中国风(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'李四',65 union all
select N'李四',77 union all
select 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+','''')+' 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),[总成绩]=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,[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,@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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!