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');

Answer 1

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