SQL group by concatenate string of a query result

Question

I have a query that joins several tables. In the result I have several fields, but I need to group by one of them concatenating the content of other field in a string.

The query result is like next table:

* query result
+-----------+-------------+
| element   |   option    |
+-----------+-------------+
|   25      |    foo 2    |
|   25      |    bar 1    |
|   25      |    baz 1    |
|   30      |    foo 2    |
|   30      |    baz 5    |
|   32      |    baz 1    |
+-----------+-------------+

I have done similar things before with GROUP_CONCAT like this:

SELECT
  result.element,
  GROUP_CONCAT(result.options SEPARATOR ', ') AS 'options'
FROM (
  -- place here an sql query with joins and some calculated fields --
) AS result
GROUP BY result.element

And it usually works, but it seems that the sql server that I have to do this query now, does not support GROUP_CONCAT.

The sql server version is Microsoft SQL Server 2014 (SP2-CU8) (KB4037356) - 12.0.5557.0 (X64) Standard Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)

What I need in the end is something like this:

* final result
+-----------+-----------------------------+
| element   |   option                    |
+-----------+-----------------------------+
|   25      |    foo 2, bar 1, baz 1      |
|   30      |    foo 2, baz 5             |
|   32      |    baz 1                    |
+-----------+-----------------------------+

I've searched a lot and I found a way to do this directly from a table, but not from another query result. How it can be done?

EDIT: please, remember that I have to do the xml path from a query result, not from a table. I understand how to use it from a table, but I do not understand how to use the xml path from a query result.

If I use something like:

SELECT
  result.element,
  ( SELECT STUFF((SELECT ',' + options
    FROM result T2
    WHERE T2.element= result.element
    ORDER BY element
    FOR XML PATH('')), 1, 1, '') )AS 'options'
FROM (

  SELECT 
    st.element AS 'element',
    CONCAT(st.salesoriginid, ' ', COUNT(st.salesoriginid)) AS 'options'

  FROM SALESTABLE AS st WITH (NOLOCK)
  LEFT JOIN SALESLINE AS sl WITH (NOLOCK) ON sl.SALESID = st.SALESID AND sl.DATAAREAID = st.DATAAREAID
  LEFT JOIN INVENTDIM AS idim WITH (NOLOCK) ON idim.INVENTDIMID = sl.INVENTDIMID AND idim.DATAAREAID = sl.DATAAREAID

  WHERE st.salestype = 3
    AND st.salesoriginid IS NOT NULL
    AND st.salesoriginid != ''

  GROUP BY st.element, st.salesoriginid

) AS result
GROUP BY result.element

Then I get error:

Invalid object name 'result' [SQL State=S0002, DB Errorcode=208]

SQL Server does not support `GROUP_CONCAT`. See [here](https://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server) for alternates. — Peter Smith, Jul 10 '19 at 08:23
Possible duplicate of [How Stuff and 'For Xml Path' work in Sql Server](https://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server) — Amira Bedhiafi, Jul 10 '19 at 08:28
*"but not from another query result"* what does this mean? Can you show the sample in your question please — huMpty duMpty, Jul 10 '19 at 08:41
@huMpty duMpty I mean that the origin of the data is no a table, is something like: `SELECT st.element AS 'element', CONCAT(st.salesoriginid, ' ', COUNT(st.salesoriginid)) AS 'options' FROM SALESTABLE AS st WITH (NOLOCK) LEFT JOIN SALESLINE AS sl WITH (NOLOCK) ON sl.SALESID = st.SALESID AND sl.DATAAREAID = st.DATAAREAID LEFT JOIN INVENTDIM AS idim WITH (NOLOCK) ON idim.INVENTDIMID = sl.INVENTDIMID AND idim.DATAAREAID = sl.DATAAREAID WHERE st.salestype = 3 AND st.salesoriginid IS NOT NULL GROUP BY st.element, st.salesoriginid` — Alex, Jul 10 '19 at 09:00
@Alex: So you can try creating temp table and execute this as [dynamic sql](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-2017) and then writing the final query in temp table — huMpty duMpty, Jul 10 '19 at 09:06
@huMpty duMpty maybe it's something wrong with the link, it's for an execute transact sql for a sql server 2017. I've never used dynamic sql.... I think I should search and read aboput it — Alex, Jul 10 '19 at 09:33
@Alex here you go small sample. `Create Table #T(Id int, Something varchar(10)) Declare @Query varchar(max) = 'Select 10, ''Desc1'' Union Select 11, ''Desc2''' SET @Query = 'INSERT INTO #t ' + @Query exec (@Query) sELECT * FROM #t` — huMpty duMpty, Jul 10 '19 at 10:18
Possible duplicate of [SQL Server 2008 concatenate rows to column](https://stackoverflow.com/questions/27922448/sql-server-2008-concatenate-rows-to-column) — Pred, Jul 10 '19 at 11:13

score 1 · Answer 1 · answered Jul 10 '19 at 08:26

1

You can use STUFF

Select Distinct element, (
SELECT STUFF((SELECT ',' +option
FROM #T T2
Where T2.element = T1.element
ORDER BY element
FOR XML PATH('')), 1, 1, '') )AS [Options]
From #T T1

answered Jul 10 '19 at 08:26

huMpty duMpty

14,346
14
60
99

score 0 · Answer 2 · answered Jul 10 '19 at 08:22

0

This should work:

select element,option= stuff((select ',' + option from table t1 where 
t1.element=t2.element for xml path ('')),'',1,1) from table t2
group by element

answered Jul 10 '19 at 08:22

Red Devil

2,343
2
21
41

score 0 · Answer 3 · answered Jul 10 '19 at 11:07

How about using a CTE?

with t as (
      <your query here>
     )
select e.element,
       stuff( (select ',' + t2.option
               from t t2
               where t2.element = e.element
               for xml path ('')
              ), 1, 1, ''
            ) as options
from (select distinct element from t) e;

You can probably simplify this by pulling the elements directly from a base table.

SQL group by concatenate string of a query result

3 Answers3