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]

Answer 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

Answer 2

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

Answer 3

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.