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