select from multiple comma separated string

• fanindrabhortakke

  Old Hand

  Points: 349

  More actions

  May 13, 2012 at 3:29 am

  #72451

  declare @str1 as VARCHAR(100)='2,5,4,3,7,7'

  declare @str2 as VARCHAR(100)='5,6,7,8,9,0'

  declare @str3 as VARCHAR(100)='2,5,4,3,7,7'

  I want output from select like

  2 5 2

  5 6 5

  4 7 4

  3 8 3

  7 9 7

  7 0 7

  How can I do this ?

• Jeff Moden

  SSC Guru

  Points: 1004615

  More actions

  May 13, 2012 at 10:23 am

  #1486871

  Like this...

  SELECT f1.ItemNumber,

  Str1 = f1.Item,

  Str2 = f2.Item,

  Str3 = f3.Item

  FROM dbo.DelimitedSplit8K(@str1,',') f1

  INNER JOIN dbo.DelimitedSplit8K(@str2,',') f2

  ON f1.ItemNumber = f2.ItemNumber

  INNER JOIN dbo.DelimitedSplit8K(@str3,',') f3

  ON f1.ItemNumber = f3.ItemNumber

  ;

  Get the "DelimitedSplit8K" function from the "resources" section near the end of the following article.

  http://www.sqlservercentral.com/articles/Tally+Table/72993/

  Let us know if you have any questions on it all.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)