Concatenate strings from subquery

Question

I don't know how to concatenate string from subquery. Please note - my inner query is much more complicated. I've omitted long WHERE conditions to make things more simple - I need to select from outer query. And here is the problem - I've managed to select from all columns except from MEEV_NTO - I need to concatenate strings from this query and separate them with commas.

Please note - I've also read this - but it selects from table, not from subquery.

My query looks like this:

SELECT DISTINCT MERC_KEY, MEEV_QUA_SUM, MEEV_NTO
FROM
(
    SELECT MERC_KEY, SUM(MEEV_QUA) OVER(PARTITION BY MERC_KEY) MEEV_QUA_SUM, MEEV_NTO FROM @tbl1
) A

Desired output:

+----------+--------------+-----------------------------------+
| MERC_KEY | MEEV_QUA_SUM |             MEEV_NTO              |
+----------+--------------+-----------------------------------+
|        1 |           14 | FIRST TEST,SECOND TEST,THIRD TEST |
|        2 |            6 | ANOTHER, AND ANOTHER              |
+----------+--------------+-----------------------------------+

Whole example:

declare @tbl1 table
(
    MERC_KEY INT,   
    MEEV_QUA INT,
    MEEV_NTO VARCHAR(100)
)

INSERT @tbl1 (MERC_KEY, MEEV_QUA, MEEV_NTO)
VALUES(1, 4, 'FIRST TEST'),
(1, 3, 'SECOND TEST'),
(1, 7, 'THIRD TEST'),
(2, 2, 'ANOTHER'),
(2, 4, 'AND ANOTHER')

This is one of the very few things MySql does better (`group_concat()` would make this much easier). — Joel Coehoorn, Oct 28 '19 at 03:46
@JoelCoehoorn very being the key word here :-). SQL Server jumped on the wagon fashionably late only in it's 2017 version. — Zohar Peled, Oct 28 '19 at 08:27

score 2 · Accepted Answer · answered Oct 28 '19 at 03:50

2

If you are using SQL Server 2017 or later, you may use STRING_AGG here:

SELECT
    MERC_KEY,
    SUM(MEEV_QUA) AS MEEV_QUA_SUM,
    STRING_AGG(MEEV_NTO, ',') MEEV_NTO
FROM @tbl1
GROUP BY
    MERC_KEY;

answered Oct 28 '19 at 03:50

Tim Biegeleisen

502,043
27
286
360

score 1 · Answer 2 · answered Oct 28 '19 at 03:57

You can use aggregate functions for sub-query just like for tables. But in this case, you can use it for @tbl1, the query should look like below:

SELECT DISTINCT MERC_KEY, MEEV_QUA_SUM, MEEV_NTO
FROM
(
    SELECT MERC_KEY, SUM(MEEV_QUA) OVER(PARTITION BY MERC_KEY) MEEV_QUA_SUM, STRING_AGG(MEEV_NTO,',') AS MEEV_NTO FROM @tbl1 GROUP BY MERC_KEY
) A

You told that your sub-query is much more complicated so I keep your query structure.

Concatenate strings from subquery

2 Answers2