将列表汇总为逗号分隔的字符串 [英] Summarize the list into a comma-separated string

问题描述

这是可以每天更改的当前结果

This is the current result that can be changed from day to day

    (int)   (nvarchar)
    Number   Grade
    --------------
         1       a
         1       c
         2       a
         2       b
         2       c
         3       b
         3       a

我需要帮助的是在下面实现这个结果.

What I need help is to achieve this result below.

Number      Grade
-----------------
     1       a, c
     2    a, b, c
     3       b, a

推荐答案

使用:

declare @t table(Number int, Grade varchar)

insert @t values(1, 'a'), (1, 'c'), (2, 'a'), (2, 'b'), (2, 'c'),
(3, 'b'), (3, 'a')

select t1.Number
    , stuff((
        select ',' + Grade
        from @t t2
        where t2.Number = t1.Number
        for xml path(''), type
    ).value('.', 'varchar(max)'), 1, 1, '') [values]
from @t t1
group by t1.Number

这篇关于将列表汇总为逗号分隔的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！