CONCAT(column) OVER(PARTITION BY...)? Group-concatentating rows without grouping the result itself

Question

I need a way to make a concatenation of all rows (per group) in a kind of window function like how you can do COUNT(*) OVER(PARTITION BY...) and the aggregate count of all rows per group will repeat across each particular group. I need something similar but a string concatenation of all values per group repeated across each group.

Here is some example data and my desired result to better illustrate my problem:

grp  |  val
------------
1    |  a
1    |  b
1    |  c
1    |  d
2    |  x
2    |  y
2    |  z

And here is what I need (the desired result):

grp  |   val  |  groupcnct
---------------------------------
1    |   a    |  abcd
1    |   b    |  abcd
1    |   c    |  abcd
1    |   d    |  abcd
2    |   x    |  xyz
2    |   y    |  xyz
2    |   z    |  xyz

Here is the really tricky part of this problem:

My particular situation prevents me from being able to reference the same table twice (I'm actually doing this within a recursive CTE, so I can't do a self-join of the CTE or it will throw an error).

I'm fully aware that one can do something like:

SELECT      a.*, b.groupcnct
FROM        tbl a
CROSS APPLY (
            SELECT STUFF((
                        SELECT '' + aa.val 
                        FROM   tbl aa
                        WHERE  aa.grp = a.grp
                        FOR XML PATH('')
                   ), 1, 0, '') AS groupcnct
            ) b

But as you can see, that is referencing tbl two times in the query.

I can only reference tbl once, hence why I'm wondering if windowing the group-concatenation is possible (I'm a bit new to TSQL since I come from a MySQL background, so not sure if something like that can be done).

Create Table:

CREATE TABLE tbl
    (grp int, val varchar(1));

INSERT INTO tbl
    (grp, val)
VALUES
    (1, 'a'),
    (1, 'b'),
    (1, 'c'),
    (1, 'd'),
    (2, 'x'),
    (2, 'y'),
    (2, 'z');

@PankajGarg It's actually a result, not a table. The query is already being done in a recursive CTE, so using CTEs is not an option. The reason why I need this is because I'm trying to keep track of values from previous recursion layers as I recurse through the table using a CTE. — Zane Bien, Aug 19 '12 at 05:14
This question doesn't make a lot of sense without the recursive query. You can always output the result of the query in a temporary table, and do group concat on that. — Andomar, Aug 19 '12 at 05:22
Citing the recursive version to point out that it is not good to choose that approach: http://stackoverflow.com/questions/11137075/which-is-the-best-way-to-form-the-string-value-using-column-from-a-table-with-ro/11138695#11138695 It's better to use [`XML PATH`](http://stackoverflow.com/questions/11137075/which-is-the-best-way-to-form-the-string-value-using-column-from-a-table-with-ro/11138267#11138267) — Michael Buen, Aug 19 '12 at 08:04

score 3 · Answer 1 · answered Jun 04 '18 at 13:28

3

In sql 2017 you can use STRING_AGG function:

SELECT STRING_AGG(T.val, ',') AS val
    , T.grp
FROM @tbl AS T
GROUP BY T.grp

answered Jun 04 '18 at 13:28

Rade Vojvodic

124
4

To make your answer better you should explain what purpose the `STRING_AGG` function serves. – rst-2cv Jun 04 '18 at 13:55

score 1 · Answer 2 · edited May 23 '17 at 10:28

1

I tried using pure CTE approach: Which is the best way to form the string value using column from a Table with rows having same ID? Thinking it is faster

But the benchmark tells otherwise, it's better to use subquery(or CROSS APPLY) results from XML PATH as they are faster: Which is the best way to form the string value using column from a Table with rows having same ID?

edited May 23 '17 at 10:28

Community

1
1

answered Aug 19 '12 at 07:37

Michael Buen

38,643
9
94
118

score 1 · Answer 3 · answered May 07 '15 at 13:13

DECLARE @tbl TABLE
(
    grp INT
    ,val VARCHAR(1)
); 

BEGIN
 INSERT INTO @tbl(grp, val)
VALUES
    (1, 'a'),
    (1, 'b'),
    (1, 'c'),
    (1, 'd'),
    (2, 'x'),
    (2, 'y'),
    (2, 'z');
END;
----------- Your Required Query
SELECT  ST2.grp, 
SUBSTRING(
            (
                SELECT ','+ST1.val  AS [text()]
                FROM @tbl ST1
                WHERE ST1.grp = ST2.grp
                ORDER BY ST1.grp
                For XML PATH ('')
            ), 2, 1000
       ) groupcnct
FROM @tbl ST2

Please always explain why you think your code solves the OP's problem. The "why" is much more interesting than just a fix. — Gert Arnold, May 07 '15 at 19:11

score 0 · Answer 4 · answered Jun 01 '18 at 22:38

Is it possible for you to just put your stuff in the select instead or do you run into the same issue? (i replaced 'tbl' with 'TEMP.TEMP123')

Select 
A.*
, [GROUPCNT] = STUFF((
                        SELECT '' + aa.val 
                        FROM  TEMP.TEMP123 AA
                        WHERE  aa.grp = a.grp
                        FOR XML PATH('')
                   ), 1, 0, '') 


 from TEMP.TEMP123 A

This worked for me -- wanted to see if this worked for you too.

score 0 · Answer 5 · edited Sep 17 '19 at 21:28

I know this post is old, but just in case, someone is still wondering, you can create scalar function that concatenates row values.

IF OBJECT_ID('dbo.fnConcatRowsPerGroup','FN') IS NOT NULL
DROP FUNCTION dbo.fnConcatRowsPerGroup
GO
CREATE FUNCTION dbo.fnConcatRowsPerGroup 
    (@grp as int) RETURNS VARCHAR(MAX)
AS 
BEGIN
    DECLARE @val AS VARCHAR(MAX)

    SELECT @val = COALESCE(@val,'')+val
    FROM tbl
    WHERE grp = @grp

    RETURN @val;
END
GO


select *, dbo.fnConcatRowsPerGroup(grp)
from tbl

Here is the result set I got from querying a sample table:

grp  |   val  |  (No column name)
---------------------------------
1    |   a    |  abcd
1    |   b    |  abcd
1    |   c    |  abcd
1    |   d    |  abcd
2    |   x    |  xyz
2    |   y    |  xyz
2    |   z    |  xyz

CONCAT(column) OVER(PARTITION BY...)? Group-concatentating rows without grouping the result itself

Here is the really tricky part of this problem:

Create Table:

5 Answers5

Linked