Sql查询获取用逗号分隔的值 [英] Sql query to get the values separated by comma

问题描述

我有一个包含2列的表格



id&颜色



这些值的价值如下： -



id color

1 black

1 white

1 blue

2 red

3 magenta

3紫色



所需输出为： -

id颜色

1黑色;白色;蓝色

2红色

3品红色;紫色



请帮忙。



我尝试了什么：



我尝试使用逗号分隔的东西函数。

Hi,

I have a table consisting of 2 columns

id & colour

the values for these are as follows:-

id color
1 black
1 white
1 blue
2 red
3 magenta
3 purple

The output required is :-
id colour
1 black;white;blue
2 red
3 magenta;purple

Please help.

What I have tried:

I have tried using stuff function using comma separated.

推荐答案

你在 STUFF 的正确轨道上，你需要做的是把它放到 SubQuery中



概念证明

You are on the right track with STUFF, and what you need to do is to put that into a SubQuery

Proof of Concept

DECLARE @table TABLE (ID int, color varchar(16))

INSERT @table VALUES (1, 'black'),  ( 1, 'white'),  ( 1, 'blue'),  ( 2, 'red'),  ( 3, 'magenta'),  ( 3, 'purple')

SELECT  t.ID
  ,     Colors = STUFF(
          (  SELECT  ', ' + s.color 
             FROM    @table s
             WHERE  s.ID = t.ID
             FOR XML PATH('')
          ), 1, 1, ''
        )

FROM      @table t
GROUP BY  t.ID
ORDER BY  1

返回

ID  Colors
==  ===================  
1   black, white, blue
2   red
3   magenta, purple

试试这个：

Try this:

SELECT ID, 
       Color = STUFF((SELECT ', ' + Color
                      FROM MyTable b 
                      WHERE b.Id = a.Id 
                      FOR XML PATH('')), 1, 2, '')
FROM MyTable a
GROUP BY Id

这里记录了几种方法：

在Transact-SQL中连接行值 - 简单对话 [ ^ ]



例如：

Several methods documented here:
Concatenating Row Values in Transact-SQL - Simple Talk[^]

Eg:

SELECT
    t1.id,
    STUFF
    (
        (
            SELECT ';' + t2.color
            FROM YourTable As t2
            WHERE t2.id = t1.id
            ORDER BY t2.color
            FOR XML PATH(''), TYPE
        ).value('.', 'varchar(max)'),
        1, 1, ''
    ) As color
FROM
    YourTable As t1
GROUP BY
    id
;

在Sql Server中如何使用和'对于Xml路径'工作 - 堆栈溢出 [ ^ ]



或，如果您使用的是SQL Server 2017或更高版本：

STRING_AGG（Transact-SQL） - SQL Server | Microsoft Docs [ ^ ]

How Stuff and 'For Xml Path' work in Sql Server - Stack Overflow[^]

Or, if you're using SQL Server 2017 or later:
STRING_AGG (Transact-SQL) - SQL Server | Microsoft Docs[^]

SELECT
    id,
    STRING_AGG(color, ';') As color
FROM
    YourTable
GROUP BY
    id
;

这篇关于Sql查询获取用逗号分隔的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！