a表有两个字段 a b a 3 a 6 b 10 b表有两个字段 a b a 7 a 5 b 8 从这两个表中得出这样的结果: a b c a 9 10 b 12 8 -- 两个表A列要有相同的唯一值才行,这样可以union一下
------------------------------------ -- Author:Flystone -- Version:V1.001 -- Date:2008-08-05 23:34:35 -------------------------------------- Test Data: ta If object_id('ta') is not null Drop table ta Go Create table ta(a nvarchar(2),b int) Go Insert into ta select 'a',3 union all select 'a',6 union all select 'b',10 Go -- Test Data: tb If object_id('tb') is not null Drop table tb Go Create table tb(a nvarchar(2),b int) Go Insert into tb select 'a',7 union all select 'a',5 union all select 'b',8 Go --Start declare @s varchar(1000) select @s = isnull(@s+',','') + '['+a+']= sum(case when a = '''+a+''' then b else 0 end)' from (select distinct a from ta ) aexec('select ''a'' as o,'+ @s+ ' from ta union all select ''b'','+@s + ' from tb')--Result: /* o a b ---- ----------- ----------- a 9 10 b 12 8 */ --End
declare @ta table (a char(1),b int) insert into @ta select 'a', 3 union select 'a', 6 union select 'b', 10 declare @tb table (a char(1),b int) insert into @tb select 'a', 7 union select 'a', 5 union select 'b', 8if object_id('tempdb..#') is not null drop table #select 'a' as t,a,sum(b) as b into # from @ta group by a union all select 'b' as t,a,sum(b) as b from @tb group by adeclare @sql varchar(4000) set @sql = 'select t' select @sql=@sql+',sum(case when a='''+a+''' then b else 0 end) as '+a from (select distinct a from #) a order by a set @sql = @sql + ' from # group by t'exec(@sql)/* t a b ---- ----------- ----------- a 9 10 b 12 8(所影响的行数为 2 行)*/
--还是给你个简单的例子吧 DECLARE @t TABLE(Year int,Quarter int,Amount decimal(10,1)) INSERT @t SELECT 1990, 1, 1.1 UNION ALL SELECT 1990, 2, 1.2 UNION ALL SELECT 1990, 3, 1.3 UNION ALL SELECT 1990, 4, 1.4 UNION ALL SELECT 1991, 1, 2.1 UNION ALL SELECT 1991, 2, 2.2 UNION ALL SELECT 1991, 3, 2.3 UNION ALL SELECT 1991, 4, 2.4--查询处理 SELECT Year, Q1=SUM(CASE Quarter WHEN 1 THEN Amount END), Q2=SUM(CASE Quarter WHEN 2 THEN Amount END), Q3=SUM(CASE Quarter WHEN 3 THEN Amount END), Q4=SUM(CASE Quarter WHEN 4 THEN Amount END) FROM @t GROUP BY Year/*--结果 Year Q1 Q2 Q3 Q4 --------- ------------------------------ 1990 1.1 1.2 1.3 1.4 1991 2.1 2.2 2.3 2.4 --*/
a b
a 3
a 6
b 10
b表有两个字段
a b
a 7
a 5
b 8 从这两个表中得出这样的结果:
a b c
a 9 10
b 12 8
--
两个表A列要有相同的唯一值才行,这样可以union一下
-- Author:Flystone
-- Version:V1.001
-- Date:2008-08-05 23:34:35
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(a nvarchar(2),b int)
Go
Insert into ta
select 'a',3 union all
select 'a',6 union all
select 'b',10
Go
-- Test Data: tb
If object_id('tb') is not null
Drop table tb
Go
Create table tb(a nvarchar(2),b int)
Go
Insert into tb
select 'a',7 union all
select 'a',5 union all
select 'b',8
Go
--Start
declare @s varchar(1000)
select @s = isnull(@s+',','') + '['+a+']= sum(case when a = '''+a+''' then b else 0 end)'
from (select distinct a from ta ) aexec('select ''a'' as o,'+ @s+ ' from ta union all select ''b'','+@s + ' from tb')--Result:
/*
o a b
---- ----------- -----------
a 9 10
b 12 8
*/
--End
insert into @ta
select 'a', 3 union
select 'a', 6 union
select 'b', 10 declare @tb table (a char(1),b int)
insert into @tb
select 'a', 7 union
select 'a', 5 union
select 'b', 8if object_id('tempdb..#') is not null drop table #select 'a' as t,a,sum(b) as b into # from @ta group by a
union all
select 'b' as t,a,sum(b) as b from @tb group by adeclare @sql varchar(4000)
set @sql = 'select t'
select @sql=@sql+',sum(case when a='''+a+''' then b else 0 end) as '+a from (select distinct a from #) a order by a
set @sql = @sql + ' from # group by t'exec(@sql)/*
t a b
---- ----------- -----------
a 9 10
b 12 8(所影响的行数为 2 行)*/
--还是给你个简单的例子吧
DECLARE @t TABLE(Year int,Quarter int,Amount decimal(10,1))
INSERT @t SELECT 1990, 1, 1.1
UNION ALL SELECT 1990, 2, 1.2
UNION ALL SELECT 1990, 3, 1.3
UNION ALL SELECT 1990, 4, 1.4
UNION ALL SELECT 1991, 1, 2.1
UNION ALL SELECT 1991, 2, 2.2
UNION ALL SELECT 1991, 3, 2.3
UNION ALL SELECT 1991, 4, 2.4--查询处理
SELECT Year,
Q1=SUM(CASE Quarter WHEN 1 THEN Amount END),
Q2=SUM(CASE Quarter WHEN 2 THEN Amount END),
Q3=SUM(CASE Quarter WHEN 3 THEN Amount END),
Q4=SUM(CASE Quarter WHEN 4 THEN Amount END)
FROM @t
GROUP BY Year/*--结果
Year Q1 Q2 Q3 Q4
--------- ------------------------------
1990 1.1 1.2 1.3 1.4
1991 2.1 2.2 2.3 2.4
--*/