从两个表中获得特定格式的统计数据集

[SoHo_Andy]

我有两个表
表1：name1
字段：name,code
（name为姓名，code为代码）

表2：num1
字段:id,code,总数,type
（id为自增字段，没有其它含义，code是和表1中对应的代码,
总数为code对应的一个值，type为code对应的类别）

数据内容如下 （暂定姓名和编码都没有重复）
表1
code,name1
1,张三
2,李四
3,赵五
4,王六

表2 num1
这里只有code为1,2的值，type也只有1,2,但是实际上type很多值，需要考虑在
内，不能使用type=1,2 这样直接枚举）

id,code,总数,type
1,1,800,1
2,2,500,1
3,1,200,1
4,2,500,1
5,1,100,2
6,2,100,2
8,2,300,2
9,2,300,2
10,1,100,2


我想得到的真实查询结果为
name,总数1,总数2，......
张三，1000,200,.....
李四,1000,700,....
赵五,null,null,....
王六,null,null,....


name 字段显示为name1表的全部记录，不能重复
总数1 字段为张三用户对应的type值为1的num1中的总数字段的总和
总数2 字段为张三用户对应的type值为2的num1中的总数字段的总和
总数3 字段以此类推，type有多少个值，张三就对应多少个总数字段


先谢谢各位朋友了！帮帮忙

[xluzhong]

http://blog.csdn.net/xluzhong/articles/349046.aspx

[fengfangfang]

交叉表
看看这个例子

USE Northwind
GO

CREATE TABLE Pivot
( Year SMALLINT,
Quarter TINYINT,
Amount DECIMAL(2,1) )
GO
INSERT INTO Pivot VALUES (1990, 1, 1.1)
INSERT INTO Pivot VALUES (1990, 2, 1.2)
INSERT INTO Pivot VALUES (1990, 3, 1.3)
INSERT INTO Pivot VALUES (1990, 4, 1.4)
INSERT INTO Pivot VALUES (1991, 1, 2.1)
INSERT INTO Pivot VALUES (1991, 2, 2.2)
INSERT INTO Pivot VALUES (1991, 3, 2.3)
INSERT INTO Pivot VALUES (1991, 4, 2.4)
GO

This is the SELECT statement used to create the rotated results:

SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year
GO

[xluzhong]

--建立测试环境
create table tb1 (code nvarchar(10),name1 nvarchar(10))
insert into tb1 select '1','张三'
union all select '2','李四'
union all select '3','赵五'
union all select '4','王六'

create table tb2 (id int identity(1,1),code nvarchar(10),总数 int,type nvarchar(10))
insert into tb2 select '1','800','1'
union all select '2','500','1'
union all select '1','200','1'
union all select '2','500','1'
union all select '1','100','2'
union all select '2','100','2'
union all select '2','300','2'
union all select '2','300','2'
union all select '1','100','2'
--查询处理

DECLARE @SQL VARCHAR(8000)
SET @SQL='select name1 '
SELECT @SQL= @SQL+',sum(case when type='+tt+' then 总数 else 0 end) ['+tt+']' from
(select distinct type as tt from tb2)b

set @sql=@sql+' from tb1 a left join tb2 b on a.code=b.code group by name1'
print @sql
exec(@sql)
go


--删除测试环境

[xluzhong]

--建立测试环境
create table tb1 (code nvarchar(10),name1 nvarchar(10))
insert into tb1 select '1','张三'
union all select '2','李四'
union all select '3','赵五'
union all select '4','王六'

create table tb2 (id int identity(1,1),code nvarchar(10),总数 int,type nvarchar(10))
insert into tb2 select '1','800','1'
union all select '2','500','1'
union all select '1','200','1'
union all select '2','500','1'
union all select '1','100','2'
union all select '2','100','2'
union all select '2','300','2'
union all select '2','300','2'
union all select '1','100','2'
--查询处理

DECLARE @SQL VARCHAR(8000)
SET @SQL='select name1 '
SELECT @SQL= @SQL+',sum(case when type='+tt+' then 总数 else 0 end) ['+'总数'+tt+']' from
(select distinct type as tt from tb2)b

set @sql=@sql+' from tb1 a left join tb2 b on a.code=b.code group by name1'
print @sql
exec(@sql)
go

--删除测试环境
Drop table tb1,tb2
/*
result
name1 总数1 总数2
李四 1000 700
王六 0 0
张三 1000 200
赵五 0 0
*/

[子陌红尘]

declare @sql varchar(8000)
set @sql = ''

select @sql = @sql +
',总数'+rtrim(type)+'=sum(case when b.type='+rtrim(type)+' then b.总数 end)'
from 表2 group by type order by type

set @sql = 'select name=a.name1'+@sql+
' from 表1 a,表2 b where a.name1=b.name group by a.name1 order by a.name1'

exec(@sql)

[SoHo_Andy]

多谢，多谢，再两位提示下，枚举已经可以实现了
如下
SELECT dbo.Name1.Name,
SUM(CASE num1.type WHEN 1 THEN num1.总数 ELSE 0 END) AS 总数1,
SUM(CASE num1.type WHEN 2 THEN num1.总数 ELSE 0 END) AS 总数2
FROM dbo.Name1 LEFT OUTER JOIN
dbo.Num1 ON dbo.Name1.code = dbo.Num1.code
GROUP BY dbo.Name1.Name
order by 总数1

结果：
name ,总数1，总数2
王六 ,0,0
赵五 ,0,0
李四 ,1000,700
张三 ,1000,200


但是我的要求是无需枚举type的值锕，继续请教

[SoHo_Andy]

多谢各位，已经全部实现了，多谢多谢

[子陌红尘]

drop table #t1
drop table #t2

create table #t1 (code nvarchar(10),name nvarchar(10))
insert into #t1 select '1','张三'
union all select '2','李四'
union all select '3','赵五'
union all select '4','王六'

create table #t2 (id int identity(1,1),code nvarchar(10),总数 int,type nvarchar(10))
insert into #t2 select '1','800','1'
union all select '2','500','1'
union all select '1','200','1'
union all select '2','500','1'
union all select '1','100','2'
union all select '2','100','2'
union all select '2','300','2'
union all select '2','300','2'
union all select '1','100','2'


declare @sql varchar(8000)
set @sql = ''

select @sql = @sql +
',总数'+rtrim(type)+'=sum(case when b.type='+rtrim(type)+' then b.总数 end)'
from #t2 group by type order by type

set @sql = 'select a.name'+@sql+
' from #t1 a,#t2 b where a.code=b.code group by a.name order by a.name'

print @sql

exec(@sql)