Is it possible to concatenate column values into a

问题:

Say I have the following table:

id|myId|Name
-------------
1 | 3  |Bob 
2 | 3  |Chet
3 | 3  |Dave
4 | 4  |Jim
5 | 4  |Jose
-------------

Is it possible to use a recursive CTE to generate the following output:

3 | Bob, Chet, Date
4 | Jim, Jose

I've played around with it a bit but haven't been able to get it working. Would I do better using a different technique?

回答1:

I do not recommend this, but I managed to work it out.

Table:

CREATE TABLE [dbo].[names](
    [id] [int] NULL,
    [myId] [int] NULL,
    [name] [char](25) NULL
) ON [PRIMARY]

Data:

INSERT INTO names values (1,3,'Bob')
INSERT INTO names values 2,3,'Chet')
INSERT INTO names values 3,3,'Dave')
INSERT INTO names values 4,4,'Jim')
INSERT INTO names values 5,4,'Jose')
INSERT INTO names values 6,5,'Nick')

Query:

WITH CTE (id, myId, Name, NameCount)
     AS (SELECT id,
                myId,
                Cast(Name AS VARCHAR(225)) Name,
                1                          NameCount
         FROM   (SELECT Row_number() OVER (PARTITION BY myId ORDER BY myId) AS id,
                        myId,
                        Name
                 FROM   names) e
         WHERE  id = 1
         UNION ALL
         SELECT e1.id,
                e1.myId,
                Cast(Rtrim(CTE.Name) + ',' + e1.Name AS VARCHAR(225)) AS Name,
                CTE.NameCount + 1                                     NameCount
         FROM   CTE
                INNER JOIN (SELECT Row_number() OVER (PARTITION BY myId ORDER BY myId) AS id,
                                   myId,
                                   Name
                            FROM   names) e1
                  ON e1.id = CTE.id + 1
                     AND e1.myId = CTE.myId)
SELECT myID,
       Name
FROM   (SELECT myID,
               Name,
               (Row_number() OVER (PARTITION BY myId ORDER BY namecount DESC)) AS id
        FROM   CTE) AS p
WHERE  id = 1 

As requested, here is the XML method:

SELECT myId,
       STUFF((SELECT ',' + rtrim(convert(char(50),Name))
        FROM   namestable b
        WHERE  a.myId = b.myId
        FOR XML PATH('')),1,1,'') Names
FROM   namestable a
GROUP BY myId

回答2:

A CTE is just a glorified derived table with some extra features (like recursion). The question is, can you use recursion to do this? Probably, but it's using a screwdriver to pound in a nail. The nice part about doing the XML path (seen in the first answer) is it will combine grouping the MyId column with string concatenation.

How would you concatenate a list of strings using a CTE? I don't think that's its purpose.

回答3:

A CTE is just a temporarily-created relation (tables and views are both relations) which only exists for the "life" of the current query.

I've played with the CTE names and the field names. I really don't like reusing fields names like id in multiple places; I tend to think those get confusing. And since the only use for names.id is as a ORDER BY in the first ROW_NUMBER() statement, I don't reuse it going forward.

WITH namesNumbered as (
    select myId, Name,
        ROW_NUMBER() OVER (
            PARTITION BY myId 
            ORDER BY id
        ) as nameNum
    FROM names
)
, namesJoined(myId, Name, nameCount) as (
    SELECT myId,
        Cast(Name AS VARCHAR(225)),
        1
    FROM namesNumbered nn1
    WHERE nameNum = 1
    UNION ALL
    SELECT nn2.myId,
        Cast(
            Rtrim(nc.Name) + ',' + nn2.Name
            AS VARCHAR(225)
        ),
        nn.nameNum
    FROM namesJoined nj
    INNER JOIN namesNumbered nn2 ON nn2.myId = nj.myId
        and nn2.nameNum = nj.nameCount + 1
)
SELECT myId, Name
FROM (
    SELECT myID, Name,
        ROW_NUMBER() OVER (
            PARTITION BY myId
            ORDER BY nameCount DESC
        ) AS finalSort
    FROM namesJoined
) AS tmp
WHERE finalSort = 1

The first CTE, namesNumbered, returns two fields we care about and a sorting value; we can't just use names.id for this because we need, for each myId value, to have values of 1, 2, .... names.id will have 1, 2 ... for myId = 1 but it will have a higher starting value for subsequent myId values.

The second CTE, namesJoined, has to have the field names specified in the CTE signature because it will be recursive. The base case (part before UNION ALL) gives us records where nameNum = 1. We have to CAST() the Name field because it will grow with subsequent passes; we need to ensure that we CAST() it large enough to handle any of the outputs; we can always TRIM() it later, if needed. We don't have to specify aliases for the fields because the CTE signature provides those. The recursive case (after the UNION ALL) joins the current CTE with the prior one, ensuring that subsequent passes use ever-higher nameNum values. We need to TRIM() the prior iterations of Name, then add the comma and the new Name. The result will be, implicitly, CAST()ed to a larger field.

The final query grabs only the fields we care about (myId, Name) and, within the subquery, pointedly re-sorts the records so that the highest namesJoined.nameCount value will get a 1 as the finalSort value. Then, we tell the WHERE clause to only give us this one record (for each myId value).

Yes, I aliased the subquery as tmp, which is about as generic as you can get. Most SQL engines require that you give a subquery an alias, even if it's the only relation visible at that point.