First, your best solution is to not store data in a comma-separated list in your database. You should consider fixing the table structure.
If you cannot alter the table structure, then you will need to split the data in the list to rows to assign the correct name. Once the data is split then you can concatenate the data back into the list.
There are many different split
function that you can find online but here is a version that I typically use:
CREATE FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))
returns @temptable TABLE (items varchar(MAX))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end;
To get your result, I would start by applying the split
function and a row_number()
since I do not see a unique key associated with each row. If you have a unique key on each row then you will not need the row_number()
:
;with cte as
(
select rn, name, id
from
(
select row_number() over(order by (select 1)) rn,
databasename
from table2
) t2
cross apply dbo.split(t2.databasename, ',') i
inner join table1 t1
on i.items = t1.id
)
select *
from cte
This query breaks your comma-separated list into the following:
| RN | NAME | ID |
--------------------
| 1 | MSSQL | 1 |
| 1 | Oracle | 3 |
| 2 | MySQl | 2 |
| 3 | MSSQL | 1 |
| 3 | MySQl | 2 |
Once you have the data in multiple rows with the correct name
, then you can use STUFF()
and FOR XML PATH
to concatenate it into the list. You full query would be similar to this:
;with cte as
(
select rn, name, id
from
(
select row_number() over(order by (select 1)) rn,
databasename
from table2
) t2
cross apply dbo.split(t2.databasename, ',') i
inner join table1 t1
on i.items = t1.id
)
select
STUFF(
(SELECT ', ' + c2.name
FROM cte c2
where c1.rn = c2.rn
order by c2.id
FOR XML PATH (''))
, 1, 1, '') Databasename
from cte c1
group by c1.rn
order by c1.rn;
See SQL Fiddle with Demo.
The result of the full query is:
| DATABASENAME |
------------------
| MSSQL, Oracle |
| MySQl |
| MSSQL, MySQl |