SQL FOR XML PATH list and COUNT

问题:

I have a table such as:

|Date         |Name|
--------------------
|'20-May-2011'|Bob |
|'20-May-2011'|Fred|
|'20-May-2011'|Jim |
|'21-May-2011'|Bob |
|'21-May-2011'|Ed  |
|'22-May-2011'|Bill|

I need a query to return:

|Date         |Count|Names           |
--------------------------------------
|'20-May-2011'|    3|'Bob, Fred, Jim'|
|'21-May-2011'|    2|'Bob, Ed'       |
|'22-May-2011'|    1|'Bill'          |

In other words, I want a list and a count of the names by date. The best I can come up with is:

SELECT list.[Date], [Count], [Names]
FROM (
    SELECT  [Date], 
            STUFF((
                SELECT ', ' + [Name]
                FROM #table t2
                WHERE t2.[Date] = t.[Date]
                ORDER BY [Name]
                FOR XML PATH('')
            ), 1, 2, '') AS [Names]
    FROM #table t
    GROUP BY [Date]
) [list]
INNER JOIN (
    SELECT  [Date], 
            COUNT(*) AS [Count]
    FROM #table t
    GROUP BY [Date]
) [count]
    ON list.[Date] = count.[Date]
ORDER BY [Count] DESC, list.[Date]

Is there a more elegant query?

回答1:

SELECT  [Date], 
        COUNT(*) AS [Count],
        STUFF((
            SELECT ', ' + [Name]
            FROM #table t2
            WHERE t2.[Date] = t.[Date]
            ORDER BY [Name]
            FOR XML PATH('')
        ), 1, 2, '') AS [Names]
FROM #table t
GROUP BY [Date]

If you think that the Name column might contain <>'"& you should do like this instead:

SELECT  [Date], 
        COUNT(*) AS [Count],
        STUFF((
            SELECT ', ' + [Name]
            FROM #table t2
            WHERE t2.[Date] = t.[Date]
            ORDER BY [Name]
            FOR XML PATH(''), TYPE
        ).value('.', 'varchar(max)'), 1, 2, '') AS [Names]
FROM #table t
GROUP BY [Date]

回答2:

Not a whole lot better - but maybe using a single CTE to "encapsulate" the XML-PATH-stuffing into a more presentable way would work??

;WITH ConsolidatedData AS
(
SELECT  
    [Date], 
    STUFF((
                SELECT ', ' + [Name]
                FROM #table t2
                WHERE t2.[Date] = t.[Date]
                ORDER BY [Name]
                FOR XML PATH('')
            ), 1, 2, '') AS [Names]
    FROM #table t
)
SELECT
    [Date], Names, COUNT(*)
FROM 
    ConsolidatedData
GROUP BY 
    [Date], Names

Not sure if you'd count this as one "compound" statement, or two.... :-)

One word of advice: try not to use SQL Server identifiers and reserved words (like Date or Order) as your own column and/or table names.... it's always rather messy....