TSQL: Join columns, but rows in one column have multiple values

Question

I have a query where I have to join two tables. Lets say T1 and T2. T1 has a column with an Id. And every row has only one Id value. However, the second table is where i'm struggling. in T2 there is a column with the Id's but it can be possible that one row has multiple of those Id's. So as example T1.col1 has this value: 737382. But T2.col2 can have this entries in one row: 737382;239112;2838210;9923834;2388342;...

I know that this structure violates the 1NF and stuff. But i cannot change anything in the data or structure of the data. now what i want to do is to join this two tables. Please let me know if i'm missing to tell any relevant information in order to answer my question. its late and my brain is exhausted ~.~

score 0 · Accepted Answer · answered Jul 18 '13 at 14:43

0

try

    select tab2.*   -- whatever
      from t1 tab1
inner join t2 tab2 on ( ';'||tab2.col2||';' like '%;'||tab1.col1||';%' )
         ;

the extra affixed ; characters serve to avoid disjunctions in the join condition.

answered Jul 18 '13 at 14:43

collapsar

17,010
4
35
61

Thanks for your reply. it seems simple so i decided to try it. however i use MSSQL Server 2008 and it doesnt work with the || operator. Tbh i dont even know what it means. could you tell me what the meaning of it is, so i can mark your post as answer? (as long as it will work) best regards – karel Aug 08 '13 at 13:21
the `||` operator is equivalent to a `concat` call. – collapsar Aug 08 '13 at 14:28
thanks, i've figured it out to myself by now.. a shame i didnt get it earlier :/ – karel Aug 08 '13 at 14:56
And yes, your answer works perfectly... just needs its time to execute. :) – karel Aug 08 '13 at 14:57

score 0 · Answer 2 · edited Jul 18 '13 at 15:07

0

Have you tried something like:

select 
    a.column1, 
    a.column2, 
    b.column1,
    b.column2 
from table a 
inner join table b on a.column1 = b.column1

edited Jul 18 '13 at 15:07

Matt Seymour

8,880
7
60
101

answered Jul 18 '13 at 14:43

Alan

1

score 0 · Answer 3 · answered Jul 18 '13 at 14:48

You could use regular expressions in your join, your regular expression can check for your T1.col1 in T2.col2. The regular expression should check for the value from the begining of the string (i.e. T2.col2) or being preceeded by ';' and always followed by ';'

score 0 · Answer 4 · answered Jul 18 '13 at 15:11

Since one T2.Col2 can hold n entries, you need to parse them to rows using a table-valued function and then using CROSS APPLY

BEWARE if the T2 is big this solution will hang for quite long time

something like this:

;WITH IDSplitted AS 
(
   SELECT * 
   FROM  T2
   CROSS APPLY dbo.[StringSplit](col2, ';')
)

SELECT * -- or whatever columns you need
FROM T1
INNER JOIN IDSplitted  ON IDSplitted.val = t1.col1

having StringSplit:

CREATE FUNCTION [dbo].[StringSplit]
(
  @delimited nvarchar(max),
  @delimiter nvarchar(100)     
) RETURNS @t TABLE
(
-- Id column can be commented out, not required for sql splitting string
  id int identity(1,1), -- I use this column for numbering splitted parts
  val nvarchar(max)
)
AS
BEGIN
  declare @xml xml
  set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'

  insert into @t(val)
  select 
    r.value('.','varchar(max)') as item
  from @xml.nodes('//root/r') as records(r)

  RETURN
END

Thanks for your answer! i'll take a look as soon as i return from vacations and can try it out! — karel, Jul 21 '13 at 17:26

score 0 · Answer 5 · answered Aug 13 '13 at 13:03

After using the solution of collapstar (which was correct) i encountered performance issues. so what i did is to create a mapping table so that when i have to run the query again, i dont have to wait so long. so i have a scheduled job which does the join and writes the output into a mapping table over night

TSQL: Join columns, but rows in one column have multiple values

5 Answers5

Linked