Concatenate values that are grouped by a column

Question

I have the following table:

Code    Ref     Value
A1      Car     A
A1      Car     -
A1      Car     B
B2      Truck   CC
B2      Truck   D
B2      Truck   -
C3      Van     E
C3      Van     F
C3      Van     -
C3      Van     G

The goal I am trying to accomplish, is a concatenated string grouping all of the values together like this:

Code   Ref    Value
A1     Car    A-B
B2     Truck  CCD-
C3     Van    EF-G

I went off of the example here, but got nowhere. Here is what I came up with:

SELECT [Table].[Code]
, [Table].[Ref]
, STUFF((SELECT DISTINCT [Value]
  FROM [Table2]
  FOR XML PATH ('')),1, 1,'') AS Values
FROM [Table]
LEFT JOIN [Table2] ON
[Table2].[Code] = [Table].[Code]

Where am I going wrong? Is there a more efficient way to do this?

Side note: without an ordering column, the concatenated result may (will) change for each run. Will that be important? — Clockwork-Muse, Apr 24 '15 at 13:50

GarethD · Accepted Answer · 2015-04-24T14:03:00.147

You have nothing linking your inner and outer references to [Table], and you also need to make the outer reference distinct. Finally you need to either have no column name within your subquery, or it needs to be [text()]

SELECT  [Code]
        ,[Ref]
        ,STUFF((SELECT DISTINCT [Value] AS [text()]
                FROM [Table] AS T2
                WHERE T1.Code = T2.Code -- LINK HERE
                AND T2.Ref = T2.Ref     -- AND HERE
                FOR XML PATH ('')
                ),1, 1,'') AS [Values]
FROM    [Table] AS T1
GROUP BY T1.Code, T1.Ref; -- GROUP BY HERE

As an aside, you do not need to use STUFF as you have no delimiter, STUFF is typically used to remove the chosen delimiter from the start of the string. So when you have a string like ,value1,value2,value3, STUFF(string, 1, 1, '') will replace the first character with '' leaving you with value1,value2,value3.

You should also use the value xquery method to ensure you are not tripped up by special characters, if you don't and you try an concatenate ">>" and "<<" you would not end up with ">><<" as you might want, you would get ">><<", so a better query would be:

SELECT  t1.Code,
        t1.Ref,
        [Values] = (SELECT  DISTINCT [text()] = [Value]
                    FROM    [Table] AS t2
                    WHERE   T1.Code = T2.Code
                    AND     T2.Ref = T2.Ref
                    FOR XML PATH (''), TYPE
                ).value('.', 'NVARCHAR(MAX)')
FROM    [Table] AS T1
GROUP BY t1.Code, t1.Ref;

ADDENDUM

Based on the latest edit to the question it appears as though your Value column is coming from another table, linked to the first table by Code. If anything this makes your query simpler. You don't need the JOIN, but you still need to make sure that there is an expression to link the outer table to the inner table your subquery. I am assuming that the rows are unique in the first table, so you probably don't need the group by either:

SELECT  t1.Code,
        t1.Ref,
        [Values] = (SELECT  DISTINCT [text()] = t2.[Value]
                    FROM    [Table2] AS t2
                    WHERE   T1.Code = T2.Code
                    FOR XML PATH (''), TYPE
                ).value('.', 'NVARCHAR(MAX)')
FROM    [Table] AS T1;

WORKING EXAMPLE

CREATE TABLE #Table1 (Code CHAR(2), Ref VARCHAR(10));
INSERT #Table1 VALUES ('A1', 'Car'), ('B2', 'Truck'), ('C3', 'Van');

CREATE TABLE #Table2 (Code CHAR(2), Value VARCHAR(2));
INSERT #Table2
VALUES ('A1', 'A'), ('A1', '-'), ('A1', 'B'),
        ('B2', 'CC'), ('B2', 'D'), ('B2', '-'),
        ('C3', 'F'), ('C3', '-'), ('C3', 'G');

SELECT  t1.Code,
        t1.Ref,
        [Values] = (SELECT  DISTINCT [text()] = t2.[Value]
                    FROM    #Table2 AS t2
                    WHERE   T1.Code = T2.Code
                    FOR XML PATH (''), TYPE
                ).value('.', 'NVARCHAR(MAX)')
FROM    #Table1 AS T1;

Okay, got this working, now if there was an index in `t2` that I need to order by, how would I go about this? I am getting an error 'ORDER BY items must appear in the select list if SELECT DISTINCT is specified.' when I add `ORDER BY t2.Index`, if I figure this part out, it's the answer I need. — Volearix, Apr 24 '15 at 14:35

Concatenate values that are grouped by a column

1 Answers1