SQL Server：如何在单行中列出表的不同值，以逗号分隔

I have the following table:

我有下表:

CREATE TABLE TEMP (ID INT, SEGMENT CHAR(1), SEGOFF INT, CHECKED SMALLDATETIME)

INSERT INTO TEMP VALUES (1,'A',0,'2009-05-01')
INSERT INTO TEMP VALUES (2,'B',1,'2009-05-01')
INSERT INTO TEMP VALUES (3,'C',0,'2009-05-01')
INSERT INTO TEMP VALUES (4,'A',0,'2009-05-02')
INSERT INTO TEMP VALUES (5,'B',2,'2009-05-02')
INSERT INTO TEMP VALUES (6,'C',1,'2009-05-02')
INSERT INTO TEMP VALUES (7,'A',1,'2009-05-03')
INSERT INTO TEMP VALUES (8,'B',0,'2009-05-03')
INSERT INTO TEMP VALUES (9,'C',2,'2009-05-03')

I would like to show distinct SEGMENT in Single row separated by comma (e.g: A,B,C)

我想在单行中用逗号分隔不同的SEGMENT(例如:A,B,C)

I try as follows:

我尝试如下:

DECLARE @SEGMENTList varchar(100)

SELECT @SEGMENTList = COALESCE(@SEGMENTList + ', ', '') + 
     SEGMENT 
FROM TEST


SELECT @SEGMENTList

It shows A, B, C, A, B, C, A, B, C

它显示A,B,C,A,B,C,A,B,C

What do I need to change my query? Thanks all!

我需要更改查询?谢谢大家!

4 个解决方案

#1

You can add a GROUP BY Segment to your select, and it might work.

您可以将GROUP BY Segment添加到您的选择中,它可能会起作用。

Like this:

DECLARE @SEGMENTList varchar(100)

SELECT @SEGMENTList = COALESCE(@SEGMENTList + ', ', '') + 
     SEGMENT 
FROM TEMP
GROUP BY SEGMENT


SELECT @SEGMENTList

#2

add a group by clause
group by segment

逐段添加group by子句

#3

SELECT @SEGMENTList = COALESCE(@SEGMENTList + ', ', '') + 
     SEGMENT
FROM 
(SELECT DISTINCT Segment FROM #TEMP) AS #Test

#4

Look at this

看这个

CREATE TABLE [dbo].[TableA](
    [id] [int] NULL,
    [type] [nvarchar](10) NULL
) ON [PRIMARY]

    declare  @temp table
(
  [type] [nvarchar](10) NULL
)

    insert into @temp
    select distinct type from TableA

    declare @s1 varchar(8000)

    update t
      set  
        @s1 = ISNULL(@s1 + ',', '') + '''' + REPLACE(t.Type, '''', '''''') + ''''
    from @temp t

    select @s1

#1