好复杂的sql处理，请教众高手

[gngnandgngn]

现在小弟在做一些sap的数据导入工作，碰到这样一个问题。

data source report：
id_no sap_no qty
1 301 1
1 302 2
2 301 1
2 303 3
3 304 4
3 305 2

destination report:
id_no cu1 cu2 cu3 cu4 cu5
1 1 2 0 0 0
2 1 0 3 0 0
3 0 0 0 4 2

转换过程中需要用到一张 sap_no和目标列名称的对应关系表，如下：
sap_no com_name
301 cu1
302 cu2
303 cu3
304 cu4
305 cu5

有行列转换的成分，但过程中又要涉及到查找另一张表，不知道这样一个东西怎么写法呢，小弟不是做数据库的，但公司没有这方面的人，所以小弟又成了冤大头，请各位高手指点一下啊，感激

[zjcxc]

--写个存储过程就可以了
create proc p_qry
as
declare @s nvarchar(4000)
set @s=''
select @s=@s+','+quotename(com_name)+'=max(case sap_no when '+quotename(sap_no,'''')+' then qty end)'
from 关系表
exec('select id_no'+@s+' from [data source report] group by id_no')
go

--调用
exec p_qry

[jinjazz]

--建立测试环境
Create Table source (id_no varchar(10),sap_no varchar(10),qty varchar(10))
--插入数据
insert into source
select '1','301','1' union
select '1','302','2' union
select '2','301','1' union
select '2','303','3' union
select '3','304','4' union
select '3','305','2'
Create Table destination (sap_no varchar(10),com_name varchar(10))
--插入数据
insert into destination
select '301','cu1' union
select '302','cu2' union
select '303','cu3' union
select '304','cu4' union
select '305','cu5'
--测试语句
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT id_no'
SELECT @SQL= @SQL+
',max(CASE WHEN sap_no = ''' + sap_no + ''' THEN qty else 0 END) [' + com_name + ']'
FROM (SELECT DISTINCT sap_no,com_name FROM destination) a
SET @SQL=@SQL+' FROM source GROUP BY id_no'
exec (@SQL)
--删除测试环境
Drop Table source
Drop Table destination
/*
id_no cu1 cu2 cu3 cu4 cu5
---------- ----------- ----------- ----------- ----------- -----------
1 1 2 0 0 0
2 1 0 3 0 0
3 0 0 0 4 2
*/

[zjcxc]

--如果还要完成导入工作，则改为
create proc p_inport
as
declare @s nvarchar(4000),@fd nvarchar(4000)
select @fd='', @s=''
select @fd=@fd+','+quotename(com_name)
,@s=@s+','+quotename(com_name)+'=max(case sap_no when '+quotename(sap_no,'''')+' then qty end)'
from 关系表
exec('insert [destination report](id_no'+@fd+')
select id_no'+@s+' from [data source report] group by id_no')
go

--调用
exec p_inport

[gngnandgngn]

我要把上述的结果直接插入现有表里去，可以实现吗

[jinjazz]

DECLARE @SQL VARCHAR(8000)
SET @SQL='insert into report SELECT id_no'
SELECT @SQL= @SQL+
',max(CASE WHEN sap_no = ''' + sap_no + ''' THEN qty else 0 END) [' + com_name + ']'
FROM (SELECT DISTINCT sap_no,com_name FROM destination) a
SET @SQL=@SQL+' FROM source GROUP BY id_no'
exec (@SQL)

[lihongyue]

存储过程：
create proc myTest
as
declare @s varchar(8000)
set @s=''
select @s=@s+'sum(case com_name when '''+com_name+''' then qty else '''' end) as '+com_name+','
from (select distinct com_name as com_name from sourceReport s,destReport d where s.sap_no = d.sap_no) t
set @s = substring(@s , 1 , len(@s) - 1) ; -- cut the last ','
set @s = 'select id_no,'+@s+' from sourceReport s,destReport d where s.sap_no = d.sap_no group by id_no' ;
print @s
exec(@s) ;
GO

[lihongyue]

FROM (SELECT DISTINCT sap_no,com_name FROM destination) a
最后那个a是什么意思啊
-------------------------------------------------------
a是子表的别名啊

[gngnandgngn]

DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT id_no'
SELECT @SQL= @SQL+
',max(CASE WHEN sap_no = ''' + sap_no + ''' THEN qty else 0 END) [' + com_name + ']'
FROM (SELECT DISTINCT sap_no,com_name FROM destination) a
SET @SQL=@SQL+' FROM source GROUP BY id_no'
exec (@SQL)


大虾能不能解释一下这段代码啊，尤其是
FROM (SELECT DISTINCT sap_no,com_name FROM destination) a
最后那个a是什么意思啊