CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012inner-join
Si8
Si8

Reputation: 9225

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

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

Upvotes: 3

Views: 156

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81930

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

Upvotes: 1

SqlZim
SqlZim

Reputation: 38023

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

Upvotes: 1

Related Questions