SQL Server: how can I list distinct value of table in a single row, separated by comma

Question

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)

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

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

Answer 1

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

Like this:

DECLARE @SEGMENTList varchar(100)

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


SELECT @SEGMENTList

Answer 2

Hello,

Look at this

or

/*

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

Answer 3

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

Answer 4

add a group by clause
group by segment