Clever Geek Handbook

How to query a table with multiple values ​​in one column with union

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 only works for rows that only have 1 record per column B

, but does not work with 2 or more records.

How can I change Sql so that it gives me the output like this:

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


source to share


2 answers


Fun with Strings and XML , here is a small (minified) technique for tokenizing data.

Generating 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')


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
+1


source


You really shouldn't be storing multiple values ​​in the same column, it will lead to poor performance when you really need to do something with those values.

Using CSV Splitter function, Jeff Moden's function and using with string concatenation method . stuff()

select ... for xml path ('') 

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 |
+-------+-------------+

link for separating lines:

<h / "> Jeff Moden's function is used to demonstrate:

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
+1


source






More articles: