How to "Implode" (de-normalize/concat) multiple columns into a single column?

Question

I have a query which outputs something like this:

+-------+----+--------------+
| F_KEY | EV | OTHER_COLUMN |
+-------+----+--------------+
| 100   | 1  | ...          |
| 100   | 2  | ...          |
| 150   | 2  | ...          |
| 100   | 3  | ...          |
| 150   | 4  | ...          |
+-------+----+--------------+

I'm sure that I've seen an aggregation function which turns it (using GROUP BY F_KEY) into something like this:

+-------+------------+--------------+
| F_KEY | ?          | OTHER_COLUMN |
+-------+------------+--------------+
| 100   | (1, 2, 3)  | ...          |
| 150   | (2, 4)     | ...          |
+-------+------------+--------------+

Means, it somehow "implodes" the values of EV together into one single field. How can I do this? Unfortunately, I don't remember the function's name.

I'm using SQL Server.

This is a simplification of my query:

SELECT
    F_KEY,
    EV,
    OTHER_COLUMN
FROM
    TABLE1
JOIN
    TABLE2 ON F_KEY = TABLE2.ID
WHERE
    EVENT_TIME BETWEEN '2011-01-01 00:00:00.000' AND '2011-12-31 23:59:59.999'
ORDER BY
    EVENT_TIME ASC

Any idea is appreciated!

There is, alas, no such function built into SQL Server. The PIVOT operator acts like this, but each value would end up in its own column, and not concatenated together. You would need to write a fairly complex function to perform what you are describing. — Philip Kelley, Nov 10 '11 at 19:17
arr... i knew it exists. But not in SQL Server... it's in MySQL and called `GROUP_CONCAT`, which doesn't exist in SQL Server. Gotta find a workaround =( — Atmocreations, Nov 10 '11 at 19:29
Indeed, Just a cursor and inserting/appending to a temporary table. Unfortunately, I have never written a cursor, so I cant write it out :D — Stuart Blackler, Nov 10 '11 at 19:29
possible duplicate of [Simulating group_concat MySQL function in MS SQL Server 2005?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005) (and **lots** of others) — Andriy M, Nov 11 '11 at 04:55

score 2 · Accepted Answer · answered Nov 10 '11 at 20:10

here is the best concatenation method, it will not expand special characters like other XML methods:

--Concatenation with FOR XML & eliminating control/encoded char expansion "& < >"
set nocount on;
declare @YourTable table (RowID int, HeaderValue int, ChildValue varchar(5))
insert into @YourTable VALUES (1,1,'CCC')
insert into @YourTable VALUES (2,2,'B<&>B')
insert into @YourTable VALUES (3,2,'AAA')
insert into @YourTable VALUES (4,3,'<br>')
insert into @YourTable VALUES (5,3,'A & Z')
set nocount off
SELECT
    t1.HeaderValue
        ,STUFF(
                   (SELECT
                        ', ' + t2.ChildValue
                        FROM @YourTable t2
                        WHERE t1.HeaderValue=t2.HeaderValue
                        ORDER BY t2.ChildValue
                        FOR XML PATH(''), TYPE
                   ).value('.','varchar(max)')
                   ,1,2, ''
              ) AS ChildValues
    FROM @YourTable t1
    GROUP BY t1.HeaderValue

OUTPUT:

HeaderValue ChildValues
----------- ---------------
1           CCC
2           AAA, B<&>B
3           <br>, A & Z

(3 row(s) affected)

score 1 · Answer 2 · answered Nov 11 '11 at 16:39

You can do it easily enough with a function: Assumes you are going to be searching the same column/table all the time. Dynamic SQL needed if you want to be able to vary the columns/tables

CREATE FUNCTION [dbo].[fn_recursion]
(@F_KEY int)  
RETURNS varchar(2000) AS 
BEGIN 

    DECLARE @ReturnVal Varchar(2000)

    SELECT @ReturnVal = COALESCE(@ReturnVal + ', ', '') + EV
    FROM TABLE2 
    WHERE @F_KEY = @F_KEY

    RETURN ISNULL(@ReturnVal,'')

END
GO


SELECT
    F_KEY,
    EV = [dbo].[fn_recursion](F_KEY),
    OTHER_COLUMN
FROM
    TABLE1
JOIN
    TABLE2 ON F_KEY = TABLE2.ID
WHERE
    EVENT_TIME BETWEEN '2011-01-01 00:00:00.000' AND '2011-12-31 23:59:59.999'
ORDER BY
    EVENT_TIME ASC

GO

DROP FUNCTION [dbo].[fn_recursion]
GO

Ann L. · Answer 3 · 2011-11-10T20:02:54.930

0

You can probably use the technique described here. (Full disclosure: that's my blog.) It talks about generating concatenated strings in T-SQL, without use of cursors.

edited Nov 10 '11 at 20:02

answered Nov 10 '11 at 19:47

Ann L.

13,760
5
35
66

This is clever and very simple indeed: ISNULL(str1 + ' ', '') + ISNULL(str2 + ' ', '') + ISNULL(str3, '') – Bas Jun 20 '17 at 08:20

score 0 · Answer 4 · answered Nov 21 '11 at 21:03

0

Look into using FOR XML PATH, you can convert it use the xml string just like a varchar, this supports a seperating character/expression as well. It will go at the end of your query but will most likely require you to use a subquery structure.

answered Nov 21 '11 at 21:03

nka

41
4

How to "Implode" (de-normalize/concat) multiple columns into a single column?

4 Answers4

Linked