动态SQL的使用例子, 行列转换.
来源:互联网 发布:网络建站公司 编辑:程序博客网 时间:2024/06/09 20:59
drop table #test
create table #test(name nvarchar(20),type nvarchar(20),category nvarchar(20))
select * from #test
insert into #test(name,type,category) values ('n1','t1','c1');
insert into #test(name,type,category) values ('n2','t1','c2');
insert into #test(name,type,category) values ('n3','t2','c1');
insert into #test(name,type,category) values ('n4','t3','c3');
insert into #test(name,type,category) values ('n5','t2','c4');
insert into #test(name,type,category) values ('n6','t3','c5');
insert into #test(name,type,category) values ('n1','t1','c1');
--select category,name,sum(case when type='t1' then 1 else 0 end),sum(case when type='t2' then 1 else 0 end),sum(case when type='t3' then 1 else 0 end) from #test group by name,category
--如果type不固定
--使用動態SQL語句
Declare @S Varchar(8000)
Select @S = 'Select category, name'
Select @S = @S + ', SUM(Case type When ''' + type + ''' Then 1 Else 0 End) As ' + type
From #TEST Group By type
Select @S = @S + ' From #TEST Group By category, name Order By category, name'
print @S
EXEC(@S)
GO
create table #test(name nvarchar(20),type nvarchar(20),category nvarchar(20))
select * from #test
insert into #test(name,type,category) values ('n1','t1','c1');
insert into #test(name,type,category) values ('n2','t1','c2');
insert into #test(name,type,category) values ('n3','t2','c1');
insert into #test(name,type,category) values ('n4','t3','c3');
insert into #test(name,type,category) values ('n5','t2','c4');
insert into #test(name,type,category) values ('n6','t3','c5');
insert into #test(name,type,category) values ('n1','t1','c1');
--select category,name,sum(case when type='t1' then 1 else 0 end),sum(case when type='t2' then 1 else 0 end),sum(case when type='t3' then 1 else 0 end) from #test group by name,category
--如果type不固定
--使用動態SQL語句
Declare @S Varchar(8000)
Select @S = 'Select category, name'
Select @S = @S + ', SUM(Case type When ''' + type + ''' Then 1 Else 0 End) As ' + type
From #TEST Group By type
Select @S = @S + ' From #TEST Group By category, name Order By category, name'
print @S
EXEC(@S)
GO
--测试数据 行转列
Create table test (name char(10),km char(10),cj int)
insert test values('张三','语文',80)
insert test values('张三','数学',86)
insert test values('张三','英语',75)
insert test values('李四','语文',78)
insert test values('李四','数学',85)
insert test values('李四','英语',77)
--查询
declare @sql varchar(8000),@s1 varchar(8000)
select @sql = '',@s1=''
select @sql = @sql+ ',['+km+']=sum(case km when '''+km+''' then cj else 0 end)'
,@s1=@s1+',sum(case km when '''+km+''' then cj else 0 end)/sum(case km when '''+km+''' then 1 else 0 end)'
from test
group by km
exec('select name=case grouping(name) when 1 then ''全班总分'' else name end'+@sql+',小计=sum(cj)
from test
group by name with rollup
union all
select ''全班平均分'''+@s1+',sum(cj)/count(distinct name)
from test')
go
--删除测试
drop table test
Create table test (name char(10),km char(10),cj int)
insert test values('张三','语文',80)
insert test values('张三','数学',86)
insert test values('张三','英语',75)
insert test values('李四','语文',78)
insert test values('李四','数学',85)
insert test values('李四','英语',77)
--查询
declare @sql varchar(8000),@s1 varchar(8000)
select @sql = '',@s1=''
select @sql = @sql+ ',['+km+']=sum(case km when '''+km+''' then cj else 0 end)'
,@s1=@s1+',sum(case km when '''+km+''' then cj else 0 end)/sum(case km when '''+km+''' then 1 else 0 end)'
from test
group by km
exec('select name=case grouping(name) when 1 then ''全班总分'' else name end'+@sql+',小计=sum(cj)
from test
group by name with rollup
union all
select ''全班平均分'''+@s1+',sum(cj)/count(distinct name)
from test')
go
--删除测试
drop table test
--MS SQL2000下月份不固定的動態寫法
Create Table TEST
(class Nvarchar(10),
name Nvarchar(10),
年份 Int,
[1月] Varchar(10),
[2月] Varchar(10),
[3月] Varchar(10))
Insert TEST Select N'一班',N'张三',2007,'5元','2元','5元'
Union All Select N'一班',N'李四',2006,'3元','0元','1元'
Union All Select N'二班',N'王五',2007,'0元','0元','1元'
GO
Declare @S Nvarchar(4000)
Select @S = ''
Select @S = @S + ' Union Select class, name, 年份, ''' + Name + ''' As 月份, [' + Name + '] As 元 From TEST '
From SysColumns Where ID = OBJECT_ID('TEST') And Name Like '%月' Order By Name
Select @S = Stuff(@S, 1, 7, '')
Print @S
EXEC(@S)
GO
Drop Table TEST
Create Table TEST
(class Nvarchar(10),
name Nvarchar(10),
年份 Int,
[1月] Varchar(10),
[2月] Varchar(10),
[3月] Varchar(10))
Insert TEST Select N'一班',N'张三',2007,'5元','2元','5元'
Union All Select N'一班',N'李四',2006,'3元','0元','1元'
Union All Select N'二班',N'王五',2007,'0元','0元','1元'
GO
Declare @S Nvarchar(4000)
Select @S = ''
Select @S = @S + ' Union Select class, name, 年份, ''' + Name + ''' As 月份, [' + Name + '] As 元 From TEST '
From SysColumns Where ID = OBJECT_ID('TEST') And Name Like '%月' Order By Name
Select @S = Stuff(@S, 1, 7, '')
Print @S
EXEC(@S)
GO
Drop Table TEST
--动态月份2005 处理如下:
--测试环境
create table tb_tb(class varchar(10),name varchar(10),年份 varchar(10),[1月] varchar(10),[2月] varchar(10),[3月] varchar(10))
insert into tb_tb select '一班','张三','2007','5元','2元','5元'
union all select '一班','李四','2006','3元','0元','1元'
union all select '二班','王五','2007','0元','0元','1元'
--计算月份:
declare @月份 varchar(100)
set @月份='';
select @月份=@月份+',['+name+']' from sys.columns where object_id=object_id('tb_tb')
and name like '%月'
set @月份=stuff(@月份,1,1,'')
--交叉表处理
exec('
select * from tb_tb
unpivot
( 金额 for 月份 in ('+@月份+')
) unpt
where 金额<>''0元''
')
--删除测试环境
drop table tb_tb
--测试环境
create table tb_tb(class varchar(10),name varchar(10),年份 varchar(10),[1月] varchar(10),[2月] varchar(10),[3月] varchar(10))
insert into tb_tb select '一班','张三','2007','5元','2元','5元'
union all select '一班','李四','2006','3元','0元','1元'
union all select '二班','王五','2007','0元','0元','1元'
--计算月份:
declare @月份 varchar(100)
set @月份='';
select @月份=@月份+',['+name+']' from sys.columns where object_id=object_id('tb_tb')
and name like '%月'
set @月份=stuff(@月份,1,1,'')
--交叉表处理
exec('
select * from tb_tb
unpivot
( 金额 for 月份 in ('+@月份+')
) unpt
where 金额<>''0元''
')
--删除测试环境
drop table tb_tb
--建立測試環境
Create Table 表1
([id] Int,
[名称] Nvarchar(20))
Insert 表1 Select 1, N'名称1'
Union All Select 2, N'名称2'
Union All Select 3, N'名称3'
Create Table 表2
([id] Int,
[时间] Nvarchar(10),
[地点] Nvarchar(10))
Insert 表2 Select 1, N'5日', N'上海'
Union All Select 1, N'9日', N'北京'
Union All Select 1, N'20日', N'天津'
Union All Select 2, N'8日', N'杭州'
Union All Select 2, N'19日', N'广州'
Union All Select 3, N'8日', N'深圳'
GO
--創建函數
Create Function F_TEST(@id Int)
ReturnS Nvarchar(4000)
As
Begin
Declare @S Nvarchar(4000)
Select @S = ''
Select @S = @S + ';' + 时间 + '-' + 地点 From 表2 Where id = @id
Select @S = Stuff(@S, 1, 1, '')
Return @S
End
GO
--測試
Select
id,
dbo.F_TEST(id) As [时间、地点]
From
表1
GO
--刪除測試環境
Drop Table 表1, 表2
Drop Function F_TEST
Create Table 表1
([id] Int,
[名称] Nvarchar(20))
Insert 表1 Select 1, N'名称1'
Union All Select 2, N'名称2'
Union All Select 3, N'名称3'
Create Table 表2
([id] Int,
[时间] Nvarchar(10),
[地点] Nvarchar(10))
Insert 表2 Select 1, N'5日', N'上海'
Union All Select 1, N'9日', N'北京'
Union All Select 1, N'20日', N'天津'
Union All Select 2, N'8日', N'杭州'
Union All Select 2, N'19日', N'广州'
Union All Select 3, N'8日', N'深圳'
GO
--創建函數
Create Function F_TEST(@id Int)
ReturnS Nvarchar(4000)
As
Begin
Declare @S Nvarchar(4000)
Select @S = ''
Select @S = @S + ';' + 时间 + '-' + 地点 From 表2 Where id = @id
Select @S = Stuff(@S, 1, 1, '')
Return @S
End
GO
--測試
Select
id,
dbo.F_TEST(id) As [时间、地点]
From
表1
GO
--刪除測試環境
Drop Table 表1, 表2
Drop Function F_TEST
- 动态SQL的使用例子, 行列转换.
- 动态SQL的使用例子, 行列转换.
- 行列转换-->动态SQL语句例子
- sql行列转换例子(动态)
- 一个SQL行列转换的例子
- 用SQL实现行列转换的一个例子
- SQL中关于case关键字的例子(行列转换)
- SQL的行列转换
- 行列转换的SQL
- 行列转换的SQL
- 使用动态SQL实现行列转换和按列递减的功能
- sql2005 行列转换的例子
- SQL 2008行列转换的pivot--产生动态列
- Sql 行列转换 动态Sql(Pivot)
- SQLSERVER行列的动态转换
- sql查询的行列转换
- SQL的行列转换示例
- sql server 行列的转换
- 罪过。我对本本动粗了。
- delphi2007/delphi4PHP下载地址
- 在函数重载和设定参数缺省值间慎重选择
- 写在清明
- java面试基础知识
- 动态SQL的使用例子, 行列转换.
- Windows开机启动过程
- 编写安全的Symbian C++游戏代码
- Lc.exe 已退出,代码 -1
- 在写dll时碰到的关于CoCreateInstance的0x800401f0问题。
- Oracle 体系结构介绍
- 还不知道该怎么用
- OO的bug,C++的bug,还是编译器的bug?
- jsp里面支持范型吗