1

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?

Boris Jovanovic
  • 97
  • 9
  • Why do you give a brief introduction to your problem that includes part of your solution, which is obviously not the way to go, because you ask a question about it here? Please provide some (fake?) sample data and desired output. (in text, not in pictures!) – Luuk Apr 05 '20 at 09:15
  • I provide the introduction because that is the obvious answer, if I didn't mention it I would get it as an answer, if I have already tried it, there is no reason to pollute the post with answers that don't work. – Boris Jovanovic Apr 05 '20 at 09:23
  • Can't you create a view that uses FOR XML PATH and the select from the view in your application? –  Apr 05 '20 at 09:40
  • No access to the database unfortunately. All I have is the online "views" that query the database. – Boris Jovanovic Apr 05 '20 at 09:46

2 Answers2

3

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;
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
2

Your sample output seems wrong as 'a/b' should come for value 2.

try the following:

declare @t table (col1  int, col2 varchar(100))
insert into @t select 1, 'a'
insert into @t select 2, 'b'
insert into @t select 2, 'a'
insert into @t select 3, 'c'

declare @final_table table (col1  int, col2 varchar(100), col2_all varchar(1000))
insert into @final_table (col1, col2)
select * from @t

declare @col2_all varchar(1000)
declare @Name sysname

update  @final_table 
SET @col2_all = col2_all = COALESCE(CASE COALESCE(@Name, '') 
                                        WHEN col1 THEN @col2_all + '/' + col2
                                        ELSE col2 END, ''), 
                                    @Name = col1;

select col1, col2_grouped = MAX(col2_all)
from @final_table
group by col1

Using CTE:

;with cte(col1,col2_grouped,rn)
as
(
  select col1, col2 , rn=ROW_NUMBER() over (PARTITION by col1 order by col1)
  from @t
)
,cte2(col1,final_grouped,rn)
as
(
select col1, convert(varchar(max),col2_grouped), 1 from cte where rn=1
union all
select cte2.col1, convert(varchar(max),cte2.final_grouped+'/'+cte.col2_grouped), cte2.rn+1
from cte2
inner join cte on cte.col1 = cte2.col1 and cte.rn=cte2.rn+1
)
select col1, MAX(final_grouped) col2_grouped from cte2 group by col1

Please see db<>fiddle here.

sacse
  • 3,634
  • 2
  • 15
  • 24
  • Yes, brilliant! I saw the coalesce solution somewhere around, I just didn't know how to implement it. There is one problem, I can't use `insert into` in the online editor, when I think about it, makes sense that the keyword is banned, can this be written using CTE-s? – Boris Jovanovic Apr 05 '20 at 10:15
  • I did find this one online, wrote it for the given solution, but the execution time is too long, I was thinking more of using your solution (COALESCE) within a CTE. – Boris Jovanovic Apr 05 '20 at 10:46
  • @BorisJovanovic how about dynamic sql? – sacse Apr 05 '20 at 11:25
  • Functions and procedures are out. – Boris Jovanovic Apr 05 '20 at 11:54
  • @BorisJovanovic does [this](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=9864dcff46018b7854d542cf756c5449) work? – sacse Apr 05 '20 at 12:33