动态SQL的使用例子, 行列转换.

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   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

 

--动态月份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 表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