SQL Server 2008 concatenate rows to column

Question

I am using SQL Server 2008, I have a dataset that look like:

FormKey        Value      Category   
-------        -----      ------
123456         Gloves     PPE
123456         Hat        PPE
123456         Scalf      PPE
123456         Boots      PPE
987654         Glasses    PPE
987654         Harness    PPE
987654         Overalls   PPE

I am trying to concatenate the Values and group by FormKey, so that I would end up with:

Formkey       Value                        Category
-------       -----                        -------
123456        Gloves, Hat, Scalf, Boots     PPE
987654        Glasses, Harness, Overalls    PPE

However, I am getting a concat of ALL of the Values for each of the Formkeys.

The code I have been using is:

SELECT frd.formresultkey AS frk
    ,STUFF((
            SELECT ', ' + fra.value
            FROM [FormResultAnswers] FRA
            INNER JOIN [FormResultDetails] FRD ON FRA.[DetailKey] = FRD.[DetailKey]
            INNER JOIN [FormResults] FR ON FRD.[FormResultKey] = FR.[FormResultKey]
            WHERE FR.FormReference = 'PPE'
                AND frd.FormElementReference = 'PPE_List'
            FOR XML path('')
            ), 1, 1, '') AS Concatted
FROM [FormResultAnswers] FRA
INNER JOIN [FormResultDetails] FRD ON FRA.[DetailKey] = FRD.[DetailKey]
INNER JOIN [FormResults] FR ON FRD.[FormResultKey] = FR.[FormResultKey]

After this I need to update a table with the concatenated value where the Formkeys match. Can anyone help please?

I bet you know it's a bad idea to have comma-delimited values stored in a column, right? — Radu Gheorghiu, Jan 13 '15 at 12:45
Thank you Matt, but the result is returning a concat of every row value for each formresultkey, I have added a group by frk on the end of the statement but am still getting every value concatted together for each individual frk. Thanks for your suggestion — user3735855, Jan 13 '15 at 12:56
possible duplicate of [How to use GROUP BY to concatenate strings in SQL Server?](http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) — Panagiotis Kanavos, Jan 13 '15 at 13:12

score 1 · Accepted Answer · answered Jan 13 '15 at 13:14

1

Some modification to your query will fetch the result. Try this.

SELECT FormKey,
       Stuff((SELECT ',' + Value
              FROM   Result b
              WHERE  a.FormKey = b.FormKey
                     AND a.Category = b.Category
              FOR xml path('')), 1, 1, '') value,
       Category
FROM   Result a
GROUP  BY FormKey,
          Category

answered Jan 13 '15 at 13:14

Pரதீப்

91,748
19
131
172

Thank you @NoDisplayName, this was where I was almost at. Thankyou very much. – user3735855 Jan 13 '15 at 14:04

score 0 · Answer 2 · answered Jan 13 '15 at 13:10

0

You can create two function to concatenate the values, like this:

IF EXISTS (SELECT  *
           FROM sys.objects
           WHERE object_id = OBJECT_ID(N'GroupValue')) 
DROP FUNCTION GroupValue;
GO
IF EXISTS (SELECT  *
           FROM sys.objects
           WHERE object_id = OBJECT_ID(N'GroupCategory')) 
DROP FUNCTION GroupCategory;
GO
CREATE FUNCTION dbo.GroupValue (@FormKey INT)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @VAL VARCHAR(MAX) = '';
    SELECT @VAL = @VAL + Value + ', '
    FROM (SELECT DISTINCT Value
          FROM YourTable
          WHERE FormKey = @FormKey) AS TT

    IF (LEN(@VAL) > 0)
        SET @VAL = LEFT(@VAL, LEN(@VAL) - 1)

    RETURN @VAL
END
GO
CREATE FUNCTION dbo.GroupCategory (@Formkey INT)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @VAL VARCHAR(MAX) = '';
    SELECT @VAL = @VAL + Category + ', '
    FROM (SELECT DISTINCT Category
          FROM YourTable
          WHERE FormKey = @FormKey) AS TT

    IF (LEN(@VAL) > 0)
        SET @VAL = LEFT(@VAL, LEN(@VAL) - 1)

    RETURN @VAL
END
GO

And here's the query:

SELECT FormKey
      ,dbo.GroupValue(FormKey) AS Value
      ,dbo.GroupCategory(FormKey) AS Category
FROM YourTable
GROUP BY FormKey;

answered Jan 13 '15 at 13:10

dario

5,149
12
28
32

This doesn't answer the question (why the existing code doesn't produce the expected result) , nor is more efficient in aggregating string values. In fact, using XML is the fastest technique for aggregating strings currently available in SQL Server by several orders of magnitude – Panagiotis Kanavos Jan 13 '15 at 13:15
@PanagiotisKanavos I don't see the question "why the existing code doensn't produce the expected result". And this produces exactly the expected result. – dario Jan 13 '15 at 13:18
There's nothing wrong with using FOR XML to create strings. Changing the method isn't necessary and in fact, the variable concatenation technique is considered unreliable. There is even a relevant [Microsoft KB article about it](http://support2.microsoft.com/kb/287515/en-us). As for speed, [this article](http://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation) shows FOR XML is 4 times faster – Panagiotis Kanavos Jan 13 '15 at 13:24
Well, I didn't say that. It's just another approach that, for his situation, works. – dario Jan 13 '15 at 13:26
The OP is already using the fastest approach. He wasn't asking for a different one – Panagiotis Kanavos Jan 13 '15 at 13:27
Thank you @King.code for taking the time to answer my question. I appreciate being shown an alternative way to achieve the result - its another piece of knowledge. Thanks again. – user3735855 Jan 13 '15 at 14:06

SQL Server 2008 concatenate rows to column

2 Answers2

Linked