Concatenate many rows into a single text string with DISTINCT clause?

Question

This is very close, but not the same as a post of "Concatenate many rows into a single text string" Concatenate many rows into a single text string?

But what I needed, because the tables/rows had many of the same names, I only wanted the DISTINCT names showing up. So with some help from the above post as well as google I was able to pull it off...

171444  ACTIVE,ACTIVE,ACTIVE,ACTIVE,ACTIVE,ACTIVE,ACTIVE,ACTIVE

169171 RETIRED,RETIRED,RETIRED,RETIRED,RETIRED

173648  RETIRED,RETIRED,RETIRED,RETIRED,INELIGIBLE,INELIGIBLE

What I want though is:

171444  ACTIVE

169171 RETIRED

173648  RETIRED,INELIGIBLE

I think I nailed it:

Select distinct ST2.EmployeeID, 
           substring((Select DISTINCT (',' + ( ST1.AccrualStatus )) AS [text()]
            From dbo.Plan2 ST1
            Where ST1.EmployeeID = ST2.EmployeeID
            For XML PATH ('')),2, 1000) [Plan2]
     From dbo.Plan2 ST2

The second DISTINCT was required in the SUBSTRING to make sure we only returned one occurrence value for each value.

I posted the solution, as I cannot answer my own question. Hope it helps others. — ransems, Jan 28 '14 at 17:45
Love to have some help, now I need to update a field in another table that shows the Concatenated items, but alas my update statements are failing on update, Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. — ransems, Jan 28 '14 at 17:47

score 1 · Answer 1 · answered Jun 23 '15 at 14:20

This answer follows on from your last comment.

Supposing you have a table consisting of at least an [EmployeeID] column and a [Statuses] VARCHAR column wide enough to contain your arbitrary string of statuses (I presume they're statuses), what you could do is:

UPDATE OT1
SET OT1.[Statuses] = OT2.[text]
FROM [OtherTable] AS OT1
JOIN (
    Select distinct ST2.EmployeeID, 
           substring((Select DISTINCT (',' + ( ST1.AccrualStatus )) AS [text()]
            From dbo.Plan2 ST1
            Where ST1.EmployeeID = ST2.EmployeeID
            For XML PATH ('')),2, 1000) [Plan2]
    From dbo.Plan2 ST2
) AS OT2([EmployeeID],[text])
    ON OT1.[EmployeeID] = OT2.[EmployeeID];

I hope this works.

score -1 · Answer 2 · edited Jul 23 '15 at 21:01

CREATE TABLE T2
    (
      pr VARCHAR(20) ,
      pt VARCHAR(20) ,
      qty INT
    );

INSERT  INTO T2
        ( pr, pt, qty )
VALUES  ( 'A', 'x1', 10 ),
        ( 'A', 'x2', 12 ),
        ( 'A', 'x1', 15 ),
        ( 'B', 'x1', 1 ),
        ( 'B', 'x5', 5 ),
        ( 'C', 'x5', 8 );


SELECT  g1.pt ,
        SUM(qty) AS SumQty ,
        STUFF(( SELECT DISTINCT
                        ', ' + g.pr
                FROM    T2 g
                WHERE   g.pt = g1.pt
              FOR
                XML PATH('')
              ), 1, 1, '') AS pr
FROM    T2 g1
GROUP BY g1.pt




CREATE TABLE T2
    (
      pr VARCHAR(20) ,
      pt VARCHAR(20) ,
      qty INT
    );

INSERT  INTO T2
        ( pr, pt, qty )
VALUES  ( 'A', 'x1', 10 ),
        ( 'A', 'x2', 12 ),
        ( 'A', 'x1', 15 ),
        ( 'B', 'x1', 1 ),
        ( 'B', 'x5', 5 ),
        ( 'C', 'x5', 8 );


SELECT  g1.pt ,
        SUM(qty) AS SumQty ,
        STUFF(( SELECT DISTINCT
                        ', ' + g.pr
                FROM    T2 g
                WHERE   g.pt = g1.pt
              FOR
                XML PATH('')
              ), 1, 1, '') AS pr
FROM    T2 g1
GROUP BY g1.pt

Result :

pt----------SumQty--------- pr

x1------------------ 26------------------A, B

x2------------------ 12------------------A

x5------------------13-------------------B, C

Concatenate many rows into a single text string with DISTINCT clause?

2 Answers2

pt----------SumQty--------- pr