my table1 is :
col1 col2
C1 john
C2 alex
C3 piers
C4 sara
and so table 2:
col1 col2
R1 C1,C2,C4
R2 C3,C4
R3 C1,C4
how to result this?:
col1 col2
R1 john,alex,sara
R2 piers,sara
R3 john,sara
please help me?
You can concatenate rows into single string using COALESCE method. This COALESCE method can be used in SQL Server version 2008 and higher. All you have to do is, declare a varchar variable and inside the coalesce, concat the variable with comma and the column, then assign the COALESCE to the variable.
The joining comma is only slightly different from the listing comma. It is used to join two complete sentences into a single sentence, and it must be followed by a suitable connecting word. The connecting words which can be used in this way are and, or, but, while and yet.
Ideally, your best solution would be to normalize Table2 so you are not storing a comma separated list.
Once you have this data normalized then you can easily query the data. The new table structure could be similar to this:
CREATE TABLE T1
(
[col1] varchar(2),
[col2] varchar(5),
constraint pk1_t1 primary key (col1)
);
INSERT INTO T1
([col1], [col2])
VALUES
('C1', 'john'),
('C2', 'alex'),
('C3', 'piers'),
('C4', 'sara')
;
CREATE TABLE T2
(
[col1] varchar(2),
[col2] varchar(2),
constraint pk1_t2 primary key (col1, col2),
constraint fk1_col2 foreign key (col2) references t1 (col1)
);
INSERT INTO T2
([col1], [col2])
VALUES
('R1', 'C1'),
('R1', 'C2'),
('R1', 'C4'),
('R2', 'C3'),
('R2', 'C4'),
('R3', 'C1'),
('R3', 'C4')
;
Normalizing the tables would make it much easier for you to query the data by joining the tables:
select t2.col1, t1.col2
from t2
inner join t1
on t2.col2 = t1.col1
See Demo
Then if you wanted to display the data as a comma-separated list, you could use FOR XML PATH
and STUFF
:
select distinct t2.col1,
STUFF(
(SELECT distinct ', ' + t1.col2
FROM t1
inner join t2 t
on t1.col1 = t.col2
where t2.col1 = t.col1
FOR XML PATH ('')), 1, 1, '') col2
from t2;
See Demo.
If you are not able to normalize the data, then there are several things that you can do.
First, you could create a split function that will convert the data stored in the list into rows that can be joined on. The split function would be similar to this:
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;
When you use the split, function you can either leave the data in the multiple rows or you can concatenate the values back into a comma separated list:
;with cte as
(
select c.col1, t1.col2
from t1
inner join
(
select t2.col1, i.items col2
from t2
cross apply dbo.split(t2.col2, ',') i
) c
on t1.col1 = c.col2
)
select distinct c.col1,
STUFF(
(SELECT distinct ', ' + c1.col2
FROM cte c1
where c.col1 = c1.col1
FOR XML PATH ('')), 1, 1, '') col2
from cte c
See Demo.
A final way that you could get the result is by applying FOR XML PATH
directly.
select col1,
(
select ', '+t1.col2
from t1
where ','+t2.col2+',' like '%,'+cast(t1.col1 as varchar(10))+',%'
for xml path(''), type
).value('substring(text()[1], 3)', 'varchar(max)') as col2
from t2;
See SQL Fiddle with Demo
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With