超强的存储过程。看不懂。。求解释。多谢~！！！

[lufy_king]

------------------------------------------------------------------------
-- Author:  happyflystone  
-- Date  :  2009-04-30 16:14:54
-- Ver:     Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) 
--          Apr 14 2006 01:12:25 
--          Copyright (c) 1988-2005 Microsoft Corporation
--          Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
--      
------------------------------------------------------------------------

-- Test Data: tA
IF OBJECT_ID('tA') IS NOT NULL 
    DROP TABLE tA
Go
CREATE TABLE tA(Tid INT,name NVARCHAR(3))
Go
INSERT INTO tA
SELECT 1,'aaa' UNION ALL
SELECT 2,'bbb' UNION ALL
SELECT 1,'ccc' UNION ALL
SELECT 2,'ddd' 
GO
 
-- Test Data: tB
IF OBJECT_ID('tB') IS NOT NULL 
    DROP TABLE tB
Go
CREATE TABLE tB(Tid INT,otherfields NVARCHAR(3))
Go
INSERT INTO tB
SELECT 1,'xxx' UNION ALL
SELECT 2,'xxx' 
GO
--Start
declare @s varchar(1000)
select @s= isnull(@s+',','')+'[name_'+ltrim(rid) +']= max(case when a.rid ='+ltrim(rid)+' then name else '''' end)'
from (
    select distinct rid 
    from (select *,rid = (select count(1) from ta where a.tid = tid and name <= a.name)
            from ta a
    )c
) a
exec('select b.tid,b.otherfields,'+@s+ ' from (select *,rid = (select count(1) from ta where a.tid = tid and name <= a.name)
            from ta a
    ) a,tb b where a.tid = b.tid group by  b.tid,b.otherfields')
--Result:
/*

tid         otherfields name_1 name_2
----------- ----------- ------ ------
1           xxx         aaa    ccc
2           xxx         bbb    ddd

*/
--End 

功能:
表A:
Tid name
1 aaa
2 bbb
1 ccc
2 ddd

表B
Tid otherfields
1 xxx
2 xxx

希望得到：
表C
Tid otherfields name1 name2 name3......
1 xxx aaa ccc
2 xxx bbb ddd
现在有个问题：
【如果在A表中没有某个Tid的项的时候，也能生成表，例如能够生成：】
Tid otherfields name1 name2 name3......
1 xxx aaa ccc
2 xxx
3 xxx ddd eee

【并帮忙解释一下这个存诸过程。。感谢】

[lufy_king]

难道就没有人看得懂吗？\
:(

[you_tube]

动态SQL

[JonasFeng]

动态SQL ,其中某些字段是根据要求，动态得到的。

[name_'+ltrim(rid) +'] 是根据RID 得到name_1等的字段。

[conan304]

召唤happyflystone

[playwarcraft]

应该把大乌龟那个一大把的答案贴出来////
召唤P梁~~~

[flairsky]

行列转换

[jinjazz]

happyflystone 。。。

[jinjazz]

某某给我发了个消息解决问题，进去一看200分已经被狙狙私吞了...

[jimwoo]

行转列的问题，把exec里的那句print出来，就看明白了

printer 'select b.tid,b.otherfields,'+@s+ ' from (select *,rid = (select count(1) from ta where a.tid = tid and name <= a.name)
            from ta a
    ) a,tb b where a.tid = b.tid group by  b.tid,b.otherfields'