編碼問題 - 我使用 Pivot、XML 還是其他東西

我有一個大腦不工作的時刻，真的需要一些幫助。這是 SQL Server 2014 標準版。

我有 3 個表 - 為簡單起見，我們將它們稱為“名稱”、“地址”和“國際”。下面的範例數據：

Name
-----
Id   Sequence   Name
---  --------   ------
101  1          Pluto
102  1          Mickey
102  2          Donald
102  3          C/O Goofy
103  1          Minnie
103  2          Sophie

Address
--------
Id      Address
-----   -------------
101     123 Somewhere
102     456 Over There St
103     987 Here Ln

International
-------------
Id      Address
----    ---------
101     Alberta
102     Germany
103     Poland

我需要將所有這些以管道分隔的格式放入以後導出到文件中。它需要看起來像這樣：（我將它們排成列以便於閱讀）

101 | Pluto  | 123 Somewhere | Alberta      |               |
102 | Mickey | Donald        | C/O Goofy    |456 Overthere  |Germany
103 | Minnie | Sophie        | 987 Here     |Poland         |

這是我一直在使用的程式碼。它的主要問題是重複值。

   CREATE TABLE name
   (
       id INT NULL
       ,seq INT NULL
       ,name VARCHAR(33) NULL
   )

   INSERT INTO dbo.name
           ( id, seq, name )
   VALUES  ( 101, 1, 'Pluto' )
          ,( 102, 1, 'Mickey' )
          ,( 102, 2, 'Donald' )
          ,( 102, 3, 'c/o Goofy' )
          ,( 103, 1, 'Minnie' )
          ,( 103, 2, 'Sophie' )

   CREATE TABLE addr
   (
       id INT NULL
       ,addr VARCHAR(33) null
   )


   INSERT INTO dbo.addr
           ( id, addr )
   VALUES   ( 101, '123 Somewhere')
           ,( 102, '456 Over There St')
           ,( 103, '987 Here Ln')

   CREATE TABLE intl
   (
        id INT NULL
       ,intlAddr VARCHAR(33) null
   )

   INSERT INTO dbo.intl
           ( id, intlAddr )
   VALUES   ( 101, 'Alberta')
           ,( 102, 'Germany')
           ,( 103, 'Poland')



   DECLARE @results TABLE
   (
    namecount CHAR(3)
   ,id INT
   ,seq INT
   ,name CHAR(40)
   ,addr CHAR(40)
   ,intlAddr CHAR(60)
   ,namepivot CHAR(10)
   ,addrpivot CHAR(10)
   ,intlPivot CHAR(20)

   )

   INSERT INTO @results (namecount, id, seq,name,addr, intlAddr, namepivot, addrpivot, intlPivot)
   SELECT    CAST(ROW_NUMBER() OVER ( PARTITION BY name.id ORDER BY name.seq) AS CHAR(3)) AS nameCount ,
                               name.id,
                               name.seq ,
                               CAST(ISNULL(name.name, '') AS CHAR(40)) AS name ,
                               CAST(ISNULL(addr.addr, '') AS CHAR(40)) AS addr ,
                               CAST(ISNULL(intl.intlAddr, '') AS CHAR(60)) AS intlAddr ,
                               'name'
                               + CAST(ROW_NUMBER() OVER ( PARTITION BY name.id ORDER BY name.id, name.seq) AS CHAR(10)) AS namePivot ,
                               'addr'
                               + CAST(ROW_NUMBER() OVER ( PARTITION BY addr.id ORDER BY addr.id ) AS CHAR(10)) AS addrpivot ,
                               'intlAddr'
                               + CAST(ROW_NUMBER() OVER ( PARTITION BY intl.id ORDER BY intl.id ) AS CHAR(20)) AS intlPivot
   FROM       name
   INNER JOIN dbo.addr ON name.id = addr.id
   INNER JOIN dbo.intl ON name.id = intl.id

   SELECT * FROM @results


   SELECT  nameCount ,
           id,
           seq,        
           namelist,
           addrlist,
           intlList
       FROM    ( SELECT    ROW_NUMBER() OVER ( PARTITION BY id ORDER BY ( SELECT 1) ) rownum ,
                           1 nameCount ,
                           pd.id,
                           pd.seq ,                        
                           STUFF((SELECT   '|' + RTRIM(res.name) 
                                   FROM     @results res
                                   WHERE    pd.id = res.id 
                           FOR   XML PATH('') , TYPE).value('(./text())[1]','varchar(max)'), 1, 1, '') AS namelist,

                           STUFF((SELECT   '|' + RTRIM(res.addr)                                       
                                   FROM     @results res
                                   WHERE    pd.id = res.id
                           FOR   XML PATH('') , TYPE).value('(./text())[1]','varchar(max)'), 1, 1, '') AS addrlist,

                           STUFF((SELECT   '|' + RTRIM(res.intlAddr)
                                   FROM     @results res
                                   WHERE    pd.id = res.id
                           FOR   XML PATH('') , TYPE).value('(./text())[1]','varchar(max)'), 1, 1, '') AS intlList
                   FROM  @results pd
               ) nest1
       WHERE   nest1.rownum = 1;

結果最終看起來像這樣：

       nameCount   id  seq namelist                    addrlist                                                intlList
       1           101 1   Pluto                       123 Somewhere                                           Alberta
       1           102 1   Mickey|Donald|c/o Goofy     456 Over There St|456 Over There St|456 Over There St   Germany|Germany|Germany
       1           103 1   Minnie|Sophie               987 Here Ln|987 Here Ln                                 Poland|Poland

有任何想法嗎？謝謝！

我需要以某種方式考慮所有 5 個“桶”。像這樣在末端的管道：

Pluto|123 Somewhere|Alberta|||
Mickey|Donald|c/o Goofy|456 Over There St|Germany|
Minnie|Sophie|987 Here Ln|Poland||

幾乎可以肯定有一種更好的方法可以做到這一點，但是這個方法很有效，並且可能會讓你開始一個更好的解決方案。

SELECT nameid.id, 
       STUFF((SELECT   '|' + RTRIM(res.name) 
               FROM     name res
               WHERE    nameid.id = res.id 
       FOR   XML PATH('') , TYPE).value('(./text())[1]','varchar(max)'), 1, 1, '') 
       + '|' + 
       STUFF((SELECT   '|' + RTRIM(res.addr)                                       
               FROM     addr res
               WHERE    nameid.id = res.id
       FOR   XML PATH('') , TYPE).value('(./text())[1]','varchar(max)'), 1, 1, '') 
       + '|' + 
       STUFF((SELECT   '|' + RTRIM(res.intlAddr)
               FROM     intl res
               WHERE    nameid.id = res.id
       FOR   XML PATH('') , TYPE).value('(./text())[1]','varchar(max)'), 1, 1, '') 

FROM (SELECT DISTINCT id FROM name) AS nameid
ORDER BY nameid.id

---

再次，可能有更好的方法來做到這一點，但是：

WITH myCTE AS (
       SELECT id, 1 as ord,  '|' + RTRIM(res.name) AS Col
       FROM     name res
       UNION ALL
       SELECT id, 2 as ord,  '|' + RTRIM(res.addr)                            
       FROM     addr res
       UNION ALL
       SELECT id, 3 as ord,  '|' + RTRIM(res.intlAddr)
       FROM     intl res
       ),
NameId AS (SELECT DISTINCT id FROM name)
SELECT nameid.id, 
       STUFF((
       SELECT TOP (6) Col FROM 
       (
           SELECT ord, Col FROM myCTE
           WHERE myCTE.id = nameid.id
           UNION ALL
           SELECT TOP (6) 4 as ord, '|' FROM sys.columns
       ) a
       ORDER BY ord
       FOR XML PATH('') , TYPE).value('.','varchar(max)'), 1, 1, '') 
FROM NameId
ORDER BY NameId.id

引用自：https://dba.stackexchange.com/questions/182723