How to query a table with multiple values in one column with join

Question

Table 1:

A       B       C
Test    1       This
Test1   1;4     That
Test2   7       What
Test3   6;2     Which
Test4   1;2;7   Where

Table 2:

X       Z
1       Sun
2       Mon
3       Tue
4       Wed
5       Thu
6       Fri
7       Sat

Sql:

Select
    t1.A,
    t2.Z
from
    [dbo].[Table 1] t1
    inner join [dbo].[Table2] t2
    on t1.B = t2.X

It works only for the rows that has only 1 entry in B column but fails at the 2 or more entries.

How can I modify the Sql so that it gives me the result like this:

A       Z
Test    Sun
Test1   Sun;Wed
Test2   Sat
Test3   Fri;Mon
Test4   Sun;Mon;Sat

What DB engine do you use? and second why store multiple values in a single column? That is bad table design. Can you change it? — juergen d, Mar 31 '17 at 16:10
I am sorry... Let me update my question. And I know, unfortunately this is the design that was given to us. — Si8, Mar 31 '17 at 16:11
This isn't a good match for an RDBMS. Retrieve the data from `Table 2`, and perform search-and-replace in memory using a language that is better suited for the problem (Java, C#, C++). — Sergey Kalinichenko, Mar 31 '17 at 16:19
Fix your data! Don't store lists of *numeric* ids as delimited *string* lists. That is simply the wrong way to store data in a relational database. — Gordon Linoff, Mar 31 '17 at 16:21
You might want to investigate a string splitter if you can't fix the table design. Here are some excellent choices. http://sqlperformance.com/2012/07/t-sql-queries/split-strings — Sean Lange, Mar 31 '17 at 16:21

score 1 · Answer 1 · answered Mar 31 '17 at 16:25

You really should not store multiple values in the same column, it will only lead to poor performance whenever you actually have to do something with those values.

Using a CSV Splitter table valued function by Jeff Moden and the using the stuff() with select ... for xml path ('') method of string concatenation. :

select
    t1.a
  , z = stuff((
      select ';'+t2.Z
      from t1 i
        cross apply dbo.delimitedsplit8K(i.b,';') s
        inner join t2 
          on s.Item = t2.x
      where i.a = t1.a
      order by s.ItemNumber
      for xml path(''),type).value('(./text())[1]','nvarchar(max)')
    ,1,1,'')
from t1

rextester demo:

returns: http://rextester.com/HNNP95095

+-------+-------------+
|   a   |      z      |
+-------+-------------+
| Test  | Sun         |
| Test1 | Sun;Wed     |
| Test2 | Sat         |
| Test3 | Fri;Mon     |
| Test4 | Sun;Mon;Sat |
+-------+-------------+

splitting strings reference:

The function by Jeff Moden used for the demo:

create function [dbo].[delimitedsplit8K] (
      @pstring varchar(8000)
    , @pdelimiter char(1)
  )
returns table with schemabinding as
 return
  with e1(N) as (
    select 1 union all select 1 union all select 1 union all 
    select 1 union all select 1 union all select 1 union all 
    select 1 union all select 1 union all select 1 union all select 1
  )
  , e2(N) as (select 1 from e1 a, e1 b)
  , e4(N) as (select 1 from e2 a, e2 b)
  , ctetally(N) as (
    select top (isnull(datalength(@pstring),0)) 
      row_number() over (order by (select null)) from e4
  )
  , ctestart(N1) as (
    select 1 union all
    select t.N+1 from ctetally t where substring(@pstring,t.N,1) = @pdelimiter
  )
  , ctelen(N1,L1) as (
    select s.N1,
      isnull(nullif(charindex(@pdelimiter,@pstring,s.N1),0)-s.N1,8000)
    from ctestart s
  )
 select itemnumber = row_number() over(order by l.N1)
      , item       = substring(@pstring, l.N1, l.L1)
   from ctelen l
;
go

score 1 · Accepted Answer · answered Mar 31 '17 at 16:55

Fun with Strings and XML, here is a little (scaled-down) technique to tokenize data.

Create Some Sample Data

Declare @Table1 table (A varchar(100),B varchar(100), C varchar(100))
Insert Into @Table1 values
('Test','1','This'),('Test1','1;4','That'),('Test2','7','What'),('Test3','6;2','Which'),('Test4','1;2;7','Where')

Declare @Table2 table (X int,Z varchar(100))
Insert Into @Table2 values
(1,'Sun'),(2,'Mon'),(3,'Tue'),(4,'Wed'),(5,'Thu'),(6,'Fri'),(7,'Sat')

The SQL

Declare @XML xml,@Str varchar(max) = (Select a,z='['+replace(b,';','];[')+']' From  @Table1 For XML Raw)
Select @Str = Replace(@Str,'['+cast(X as varchar(25))+']',Z) From  @Table2
Select @XML = @Str

Select a = r.value('@a','varchar(100)')
      ,z = r.value('@z','varchar(100)')
From  @XML.nodes('/row') as A(r)

Returns

a       z
Test    Sun
Test1   Sun;Wed
Test2   Sat
Test3   Fri;Mon
Test4   Sun;Mon;Sat

How to query a table with multiple values in one column with join

2 Answers2