连接表和连接字段

Question

使用MS-SQL2008r2，我有两个表，具有多个辅助ID，需要将其连接到一个视图(用于导出)，该视图每个辅助ID有一个行，而表数据连接到每个表的单个字段中。

tbl-1 
id | adid | ImImage1 | ImName2 
1 | 1 | Im_a |  Nm _a
2 | 1 | Im_b |  Nm _b
3 | 1 | Im_c |  Nm _c
4 | 2 | Im_x |  Nm _x
5 | 2 | Im_y |  Nm _y
6 | 2 | Im_z |  Nm _z

tbl-2 
id | adid | ImImage1 | ImName1 
1 | 1 | Im_d |  Nm _d
2 | 1 | Im_e |  Nm _e
3 | 1 | Im_f |  Nm _f
4 | 2 | Im_u |  Nm _u
5 | 2 | Im_v |  Nm _v
6 | 2 | Im_w |  Nm_w

Result

adid | ImImage1-tbl1 | ImName2-tbl1 | ImImage1-tbl2 | ImName1-tbl2 
1   Im_a, Im_b, Im_c  |  Nm _a, Nm _b, Nm _c, |  Im_d, Im_e, Im_f, | Nm _d, Nm _e, Nm _f 
2   Im_x, Im_y, Im_z  |  Nm _x, Nm _y, Nm _x  |  Im_u, Im_v, Im_w  | Nm _u, Nm _v, Nm _w

目前，我所能做的只是一个很长的列表，它看起来就像表1附加到表2(每个adid条目都有一行)。

首先创建一个视图，将每个表连接到每个adid的一行中，然后创建一个新的视图来连接它们并添加其他两个字段，这样会更好吗？还是有更有效的方法来做到这一点？

Answer 1

我认为最有效的方法是使用Server的XML扩展将行连接到一行中：

SELECT  ads.ADID,
        [ImImage1-tbl1] = STUFF(i1.val.value('.', 'NVARCHAR(MAX)'), 1, 1, ''),
        [ImName2-tbl1] = STUFF(n1.val.value('.', 'NVARCHAR(MAX)'), 1, 1, ''),
        [ImImage1-tbl2] = STUFF(i2.val.value('.', 'NVARCHAR(MAX)'), 1, 1, ''),
        [ImName1-tbl2] = STUFF(n2.val.value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM    AdTable AS Ads
        CROSS APPLY
        (   SELECT  ', ' + ImImage1
            FROM    tbl1 AS t
            WHERE   t.AdID = ads.ADID
            FOR XML PATH(''), TYPE
        ) AS i1 (val)
        CROSS APPLY
        (   SELECT  ', ' + ImName2
            FROM    tbl1 AS t
            WHERE   t.AdID = ads.ADID
            FOR XML PATH(''), TYPE
        ) AS n1 (val)
        CROSS APPLY
        (   SELECT  ', ' + ImImage1
            FROM    tbl2 AS t
            WHERE   t.AdID = ads.ADID
            FOR XML PATH(''), TYPE
        ) AS i2 (val)
        CROSS APPLY
        (   SELECT  ', ' + ImName1
            FROM    tbl2 AS t
            WHERE   t.AdID = ads.ADID
            FOR XML PATH(''), TYPE
        ) AS n2 (val);

SQL Fiddle示例