时间戳

首页 > 解决方案 > 在单行中组合/合并多列唯一值

sql-server - 在单行中组合/合并多列唯一值

问题描述

尝试将多列和多行与 SSMS 中的单个查询结合起来。这是我正在使用的更复杂表的更简单版本：

这是我当前的查询：

CREATE TABLE Table1
([C1] varchar(50), [C2] varchar(50), [C3] varchar(50))
;

INSERT INTO Table1
([C1], [C2], [C3])
VALUES
('F92', 'Game1', 'b100'),
('F92', 'Game1', 'b200'),
('F92', 'Game2', 'C200'),
('F92', 'Game2', 'D400')
;



SELECT
 C1,C2,
 STUFF(
     (SELECT ', ' + C3
      FROM Table1
      WHERE C1 = a.C1 AND C2 = a.C2
      FOR XML PATH (''))
      , 1, 1, '')  AS NamesList
FROM Table1 AS a
GROUP BY C1,C2

drop table table1

我打算得到结果：

C1   | Namelist
F92  | Game1 b100, b200 Game2 c200, d400

这在单个查询中可行吗？

标签： sql-servertsqlgroupingssmsaggregate-functions

解决方案

你当然可以做到这一点。您可以使用 cte 来获取您已经获得的分隔列表。然后获取结果并将其转换为另一个分隔列表。但是你可能想要一些东西作为组之间的分隔符而不是空格。

with FirstPass as
(
    SELECT
     C1,
     C2 + ' ' + STUFF(
         (SELECT ', ' + C3
          FROM Table1
          WHERE C1 = a.C1 AND C2 = a.C2
          FOR XML PATH (''))
          , 1, 1, '')  AS NamesList
    FROM Table1 AS a
    GROUP BY C1,C2
)

select C1
    , NameList = stuff((select ' ' + fp2.NamesList
        from FirstPass fp2
        where fp.C1 = fp2.C1
        FOR XML PATH('')), 1, 1, '')
from FirstPass fp
group by fp.C1

回报：

F92 | 游戏 1 b100、b200 游戏 2 C200、D400

- 编辑 -

有了需要按计数对 C2 进行排序的新要求，您可以简单地添加一点聚合。这与原来的变化不大。

with FirstPass as
(
    SELECT
     C1,
     C2 + ' ' + STUFF(
         (SELECT ', ' + C3
          FROM Table1
          WHERE C1 = a.C1 AND C2 = a.C2
          FOR XML PATH (''))
          , 1, 1, '')  AS NamesList
    , count(*) as GameCount
    FROM Table1 AS a
    GROUP BY C1,C2
)

select distinct C1
    , NameList = stuff((select ' ' + fp2.NamesList
        from FirstPass fp2
        where fp.C1 = fp2.C1
        group by fp2.GameCount, fp2.NamesList
        order by fp2.GameCount desc
        FOR XML PATH('')), 1, 1, '')
from FirstPass fp

推荐阅读