在TSQL中连接单列 [英] concatenating single column in TSQL

问题描述

我正在使用SSMS 2008，并尝试根据不同字段的分组将其中一行合并在一起。我有两列，people_id和address_desc。它们看起来像这样：

I am using SSMS 2008 and trying to concatenate one of the rows together based on a different field's grouping. I have two columns, people_id and address_desc. They look like this:

address_desc                         people_id
----------                           ------------ 
Murfreesboro, TN  37130              F15D1135-9947-4F66-B778-00E43EC44B9E 
11 Mohawk Rd., Burlington, MA 01803  C561918F-C2E9-4507-BD7C-00FB688D2D6E 
Unknown, UN  00000                   C561918F-C2E9-4507-BD7C-00FB688D2D6E        Jacksonville, NC  28546                  FC7C78CD-8AEA-4C8E-B93D-010BF8E4176D 
Memphis, TN  38133                   8ED8C601-5D35-4EB7-9217-012905D6E9F1 
44 Maverick St., Fitchburg, MA       8ED8C601-5D35-4EB7-9217-012905D6E9F1

现在我想连接address_desc字段/ people_id。因此，这里的第一个应该只显示address_desc的 Murfreesboro，TN 37130。但是第二个人应该只用一行而不是两行来表示address_desc的内容为 11 Mohawk Rd。，Burlington，MA 01803; Unknown，UN 00000。

Now I want to concatenate the address_desc field / people_id. So the first one here should just display "Murfreesboro, TN 37130" for address_desc. But second person should have just one line instead of two which says "11 Mohawk Rd., Burlington, MA 01803;Unknown, UN 00000" for address_desc.

做这个？我尝试使用CTE，但这给了我歧义性错误：

How do I do this? I tried using CTE, but this was giving me ambiguity error:

WITH CTE ( people_id, address_list, address_desc, length  ) 
          AS ( SELECT people_id, CAST( '' AS VARCHAR(8000) ), CAST( '' AS VARCHAR(8000) ), 0
                 FROM dbo.address_view
                GROUP BY people_id
                UNION ALL
               SELECT p.people_id, CAST( address_list + 
                      CASE WHEN length = 0 THEN '' ELSE ', ' END + c.address_desc AS VARCHAR(8000) ), 
                      CAST( c.address_desc AS VARCHAR(8000)), length + 1
                 FROM CTE c
                INNER JOIN dbo.address_view p
                   ON c.people_id = p.people_id
                WHERE p.address_desc > c.address_desc )
SELECT people_id, address_list 
      FROM ( SELECT people_id, address_list, 
                    RANK() OVER ( PARTITION BY people_id ORDER BY length DESC )
               FROM CTE ) D ( people_id, address_list, rank )
     WHERE rank = 1 ;

这是我最初的SQL查询：

Here was my initial SQL query:

SELECT a.address_desc, a.people_id 
FROM dbo.address_view a 
INNER JOIN (SELECT people_id 
FROM dbo.address_view 
GROUP BY people_id 
HAVING COUNT(*) > 1) t 
ON a.people_id = t.people_id 
order by a.people_id

推荐答案

您可以像这样使用 FOR XML PATH（''） ：

DECLARE @TestData TABLE
(
     address_desc NVARCHAR(100) NOT NULL
    ,people_id UNIQUEIDENTIFIER NOT NULL
);

INSERT  @TestData 
SELECT  'Murfreesboro, TN  37130',              'F15D1135-9947-4F66-B778-00E43EC44B9E'
UNION ALL
SELECT  '11 Mohawk Rd., Burlington, MA 01803',  'C561918F-C2E9-4507-BD7C-00FB688D2D6E'
UNION ALL
SELECT  'Unknown, UN  00000',                   'C561918F-C2E9-4507-BD7C-00FB688D2D6E'
UNION ALL
SELECT  'Memphis, TN  38133',                   '8ED8C601-5D35-4EB7-9217-012905D6E9F1'
UNION ALL
SELECT  '44 Maverick St., Fitchburg, MA',       '8ED8C601-5D35-4EB7-9217-012905D6E9F1';

SELECT  a.people_id,
    (SELECT SUBSTRING(
        (SELECT ';'+b.address_desc
        FROM    @TestData b 
        WHERE   a.people_id = b.people_id
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
        ,2
        ,4000)
    ) GROUP_CONCATENATE
FROM    @TestData a
GROUP BY a.people_id

结果：

people_id                            GROUP_CONCATENATE
------------------------------------ ------------------------------------------------------
F15D1135-9947-4F66-B778-00E43EC44B9E Murfreesboro, TN  37130
C561918F-C2E9-4507-BD7C-00FB688D2D6E 11 Mohawk Rd., Burlington, MA 01803;Unknown, UN  00000
8ED8C601-5D35-4EB7-9217-012905D6E9F1 Memphis, TN  38133;44 Maverick St., Fitchburg, MA

这篇关于在TSQL中连接单列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！