文档章节

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

深圳大道
 深圳大道
发布于 2016/12/29 15:38
字数 1576
阅读 1
收藏 0
--行列互转
/******************************************************************************************************************************************************
以学生成绩为例子,比较形象易懂

整理人:中国风(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

本文转载自:http://blog.csdn.net/smartsmile2012/article/details/8263859

深圳大道
粉丝 3
博文 877
码字总数 0
作品 0
深圳
架构师
私信 提问
for xml path 应用

读别人写的东西,无意中看到这样一句话 select @str=stuff((select ','+vcproductname from #temptable for xml path('')),1,1,'') 在sqlserver的存储过程中执行了一下可以把临时表#temptabl......

angeljing008
2018/07/03
0
0
SQL 2017 SQLPS执行Ad-SqlAvailabilityDatabase异常

SQL Server 2017 SQLPS执行Ad-SqlAvailabilityDatabase遇到问题 这个错误简直逆天,查了下该cmdlet的帮助 常规参数里有Debug。 执行Debug,输出如下: PS C:Windowssystem32> Add-SqlAvailab...

UltraSQL
2018/05/23
0
0
PHP中使用PDO连接SQL Server

注意,本文告诉你如何更好的用pdo连接sql server 2005 而不是 2000。(MSSQL2000可以直接应用PHP内置函数或直接调用PDO,另外如果应用PDO连接MSSQL 2000,需要将PHP 文件目录下的ntwdblib.dl...

Junn
2014/03/03
7.7K
0
Mybatis 分页插件 3.6.0 发布

Mybatis分页插件 - PageHelper 如果你也在用Mybatis,建议尝试该分页插件,这一定是最方便使用的分页插件。 该插件目前支持以下数据库的物理分页: Oracle Mysql MariaDB SQLite Hsqldb Post...

Liuzh_533
2015/02/04
10K
21
windows下 php5.3如何连接mssql

因为php5.3以后php官方不再支持php_mssql扩展,那么php5.3以后如何连接mssql呢。 还好,microsoft 推出了一个sqlsrv扩展。安装步骤如下: 注,安装之前请检查你的操作系统是否是下面列表中之...

logan_li
2013/02/25
4K
1

没有更多内容

加载失败,请刷新页面

加载更多

同名依赖,多次引入导致的程序错误

表现: 本地测试正常,打包上线后报错找不到某个方法(缺少依赖),检测依赖发现,同名依赖有两个版本。 解决:删除一个,程序正常

避难所
21分钟前
3
0
在HTML中的下拉框中实现超连接

<!DOCTYPE html><html lang="zh-CN"><head> <meta charset="UTF-8"> <link rel="canonical" href="https://blog.csdn.net/weixin_34228617/article/details/86130280"/> ......

mickelfeng
26分钟前
3
0
Content7关闭防火墙命令

在外部访问CentOS中部署应用时,需要关闭防火墙。 关闭防火墙命令:systemctl stop firewalld.service 开启防火墙:systemctl start firewalld.service 关闭开机自启动:systemctl disable f...

无名氏的程序员
27分钟前
3
0
分布式存储原理:TiDB

浮躁的码农
40分钟前
6
0
CSS实现圆角边框的完美解决方案

css实现图片圆角,兼容所有浏览器: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 <style type= "text/css" > /*通用样式--容器宽度值*/ .s......

前端老手
54分钟前
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部