Join Comma Delimited Data Column

join comma delimited data column

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

sql join on comma separated column

Gordon Linoff is right for the row level data storage instead of comma-separate separated.
If you have used comma separated data and size of the table is high then i have one more solution with good query performance for MS SQL Server.

Make your data comma-separated to list and then use JOIN with your required tables.

 SELECT A.OtherID,  
     Split.a.value('.', 'VARCHAR(100)') AS Data  
 FROM  
 (
     SELECT OtherID,  
         CAST ('<M>' + REPLACE(Data, ',', '</M><M>') + '</M>' AS XML) AS Data  
     FROM  Table1
 ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a); 

Joining a table based on comma separated values

Maybe this uglyness, I have not checked results:

select names.name, courses.course_name
from names inner join courses
    on ',' + names.course_ids + ',' like '%,' + cast(courses.course_id as nvarchar(20)) + ',%'

How to join comma separated column values with another table as rows

Assuming you're using SQL Server 2016, you can use string_split() to parse out your CSV column (aside: comma-separated values in a field is a sign of a poor data model) without resorting to a CTE or XML methods.

select I.inquiry_id, sup.value,V.Name
from Procure_InquiryDetails I
CROSS APPLY string_split(I.supplier_value,',') sup
join Vendor v on v.DCLink = sup.value

SQL Server - join rows into comma separated list

You are missing the condition inside the sub query.

SELECT t2.Id, STUFF((SELECT ',' + CAST(VALUE AS varchar) FROM @MyTable t1  where t1.Id =t2.ID FOR XML PATH('')), 1 ,1, '') AS ValueList
FROM @MyTable t2
GROUP BY t2.Id

Demo

Joining two datasets on comma-delimited column

Try separate_rows. First convert the factor columns to character. Then use separate_rows to unnest df, left join it to codebook and convert back. Note that the result has character columns.

library(dplyr)
library(tidyr)

df %>%
   mutate_all(as.character) %>%
   separate_rows(identifier) %>% 
   left_join(codebook %>% mutate_all(as.character), by = c("identifier" = "id")) %>% 
   group_by(data) %>% 
   summarize(identifier = toString(identifier), code = toString(code)) %>%
   ungroup

giving:

# A tibble: 5 x 3
  data  identifier code      
  <chr> <chr>      <chr>     
1 1     a          9999      
2 2     a, b       9999, 8888
3 3     b          8888      
4 4     b, c       8888, 7777
5 5     c          7777      

How to join all values for one dataset column into a comma separated string

You can do this by using LOOKUPSET() . Normally this function is for looking up a set of values in a dataset that are filtered by a specific value and returning an array of results.

In this case you don't want to filter the results so we can tell LOOKUPSET to match two identical literal values, 1 and 1. This way it will return every value in the scope specified. Once we have our array of values, we can use JOIN() to glue them all together...

So the Expression you want will look something like this.

=Join(LookupSet(1, 1, Fields!Food.Value, "DataSet"),", ")

"DataSet" is the case-sensitive name of the dataset you want to get values from, it must be surrounded with double quotes as shown.

Food is the name of the field (again case sensitive) that you want to return from the dataset.

---

Related Topics