SSRS/SQL - Concatenate Two Comma-Separated Strings

Question

In SSRS 2008 R2 SP1 I am developing a report where a user can select a @Types and an @Areas. Both of these are multi-value parameters. I need to, in either SSRS or a SQL SP, concatenate every @Areas with every @Types. For example:

@Types: DP,DPC,PC
@Areas: M1,M2,M3

Result: 'DP_M1,DPC_M1,PC_M1,DP_M2,DPC_M2,PC_M2,DP_M3,DPC_M3,PC_M3'

I cannot for the life of me figure out how to accomplish this. I've tried Join(...) and Split(Join(...)) in SSRS but can't figure out how to go from there. I can't pass an array from SSRS to SQL, so no dice in that one. I can't figure out how to go from a comma-separated string to an array in SQL. I'm going crazy. Anyone have any brilliant ideas?

Answer 1

You can use some of the methods here Turning a Comma Separated string into individual rows to get a table for each set of values and then cross join these tables together and concatenate the values.

I threw together this example modeled off the CTE answer from the link above:

declare @Types as varchar(max)
declare @Areas as varchar(max)

set @Types = 'DP,DPC,PC'
set @Areas = 'M1,M2,M3'

;with tmp(DataItem, Data) as (
select LEFT(@Types, CHARINDEX(',',@Types+',')-1),
    STUFF(@Types, 1, CHARINDEX(',',@Types+','), '')
union all
select LEFT(Data, CHARINDEX(',',Data+',')-1),
    STUFF(Data, 1, CHARINDEX(',',Data+','), '')
from tmp
where Data > ''
)

select 
    DataItem
into #Types
from tmp

;with tmp2(DataItem, Data) as (
select LEFT(@Areas, CHARINDEX(',',@Areas+',')-1),
    STUFF(@Areas, 1, CHARINDEX(',',@Areas+','), '')
union all
select LEFT(Data, CHARINDEX(',',Data+',')-1),
    STUFF(Data, 1, CHARINDEX(',',Data+','), '')
from tmp2
where Data > ''
)

select 
    DataItem
into #Areas
from tmp2

select
_Types.DataItem + '_' + _Areas.DataItem
from #Types _Types
cross join #Areas _Areas

drop table #Types
drop table #Areas

If you need to rejoin these you can see this article here for multiple examples and pick the best one for you.