Concatenate rows into columns (NO FOR XML PATH('') and recursive CTEs) - SQL Server 2012

I have a very particular problem at hand.

Brief introduction: I have two columns at a database that I need to "group concatenate", in MySQL I would simply use GROUP_CONCAT to get the desired result, in SQL Server 2017 and on I would use STRING_AGG, the problem that I have is in the SQL Server 2012, which doesn't have this function.

Now, under normal circumstances I would use FOR XML PATH('') to get the solution, this is not viable since I'm running the query from the editor inside a third source application, the error that I get is

FOR XML PATH('') can't be used inside a cursor

For the sake of the argument let's assume that it's completely out of question to use this function.

I have tried using recursive CTE, however, it's not viable due to execution time, UNION ALL takes too much resources and can't execute properly (I am using the data for reporting).

I will no post the screenshots of the data due to the sensitivity of the same, imagine just having two columns, one with an id (multiple same id's), and a column with the data that needs to be concatenated (some string). The goal is to concatenate the second columns for all of the same id's in the first columns, obviously make it distinct in the process.

Example: Input:

col1  col2 
1     a
1     b
2     a
3     c

Output:

col1  col2 
1     a/b
2     a
3     c

Does anyone have a creative idea on how to do this?

Solution

If you know the maximum number of values that need to be concatenated together, you can use conditional aggregation:

select col1,
       stuff( concat(max(case when seqnum = 1 then '/' + col2 end),
                     max(case when seqnum = 2 then '/' + col2 end),
                     max(case when seqnum = 3 then '/' + col2 end),
                     max(case when seqnum = 4 then '/' + col2 end),
                     max(case when seqnum = 5 then '/' + col2 end)
                    ), 1, 1, ''
            ) as col2s                     
from (select t.*, 
             row_number() over (partition by col1 order by col2) as seqnum
      from t
     ) t
group by col1;

You can get the maximum number using:

select top (1) count(*)
from t
group by col1;