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)AsBegin Declare @S Nvarchar(4000) Select @S = '' Select @S = @S + ';' + 时间 + '-' + 地点 From 表2 Where id = @id Select @S = Stuff(@S, 1, 1, '') Return @SEndGO--測試Select id, dbo.F_TEST(id) As [时间、地点]From 表1GO--刪除測試環境Drop Table 表1, 表2Drop Function F_TEST