Urgent! Need help on query!

• pengyunkai

  Grasshopper

  Points: 15

  More actions

  January 14, 2015 at 1:24 pm

  #293793

  Hi all, there are two tables as below:

  Table 1

  IDValue

  F001A,B,C

  F002B,C,D

  F003A,C

  Table 2

  IDValue

  D001A

  D002B

  D003C

  D004D

  what is best way to generate one table as below:

  New table

  F001D001

  F001D002

  F001D003

  F002D002

  F002D004

  F002D003

  F003D001

  F003D003

  thanks a lot in advance

  Ryan

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 14, 2015 at 1:35 pm

  #1770237

  You need to split the data in your Value column. If possible, normalize the table so you don't have to work like that.

  To split the data, you could use the DelimitedSplit8K which is explained here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

  SELECT t1.ID, t2.ID

  FROM table1 t1

  CROSS APPLY dbo.DelimitedSplit8K( t1.Value, ',') s

  JOIN table2 t2 ON s.Item = t2.Value

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  January 14, 2015 at 1:39 pm

  #1770240

  Your first table is in direct violation of first normal form (1NF) which says you can't have more than one value at any given intersection in a table.

  The article Luis referenced is about the best way to split out a list of delimited values like that. If at all possible, once you split them out keep them that way.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  January 14, 2015 at 1:45 pm

  #1770243

  This should work, but has already been mentioned you would be better off normalizing your tables

  SELECT t1.id,t2.id

  FROM table1 t1

  INNER JOIN table2 t2 ON ','+t1.value+',' LIKE '%,'+t2.value+',%'

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• pengyunkai

  Grasshopper

  Points: 15

  More actions

  January 14, 2015 at 1:57 pm

  #1770249

  All, Thank you so much for quick replies, the first table came from one excel file generate by some codes, the second table existing in database, which is reference table, what I try to do now is handle this M-M relation and generate third table in db...

  I am trying to understand this function now.

  Thanks again