Transforming Into Multiple Rows

• ywb

  SSC Enthusiast

  Points: 162

  More actions

  October 26, 2005 at 4:18 pm

  #76446

  Hi,

  I'm building a DTS package and one of the tasks it needs to do is to transform an existing order table in this format:

   UserID  OrderID

   1         0010, 003c, 0145

   2         045, x15

   3         x9010

  to a table of this format:

   UserID  OrderID

   1         0010

   1         003c

   1         0145

   2         045

   2         x15

   3         x9010

  How can I use DTS to transform a single role from the source into multiple rows in destination? Should I use ActiveX Scripting?

  Thanks,

  ywb

• Johnson J Mampoozhil

  SSC Enthusiast

  Points: 151

  More actions

  October 27, 2005 at 1:35 am

  #600479

  You could use Data Driven queries also and call an SP Which will add multiple records in the destination based on each row. In the example you have given you could do loop for coma seperated  column and insert so many records.

  Hope this will give some idea. or feel free to contact

  Johnson

   

   

• David Burrows

  SSC Guru

  Points: 65143

  More actions

  October 27, 2005 at 5:33 am

  #600525

  The following will split the data as you require (based on Remi's universal split query)

  SELECT o.UserID,

  LTRIM(SUBSTRING(',' + o.OrderID + ',', n.number + 1,

  CHARINDEX(',', ',' + o.OrderID + ',', n.number + 1) - n.number - 1)) as [OrderID]

  FROM master.dbo.spt_values n

  INNER JOIN [order] o

  ON SUBSTRING(',' + o.OrderID + ',', n.number, 1) = ','

  AND n.number < LEN(',' + o.OrderID + ',')

  WHERE n.type = 'P' AND n.number > 0

  It is limited to 255 entries in the columns and uses the undocumented system table master.dbo.spt_values (which may not be present in future releases). You could replace this with your own number table (as Remi does)

  Far away is close at hand in the images of elsewhere.
  Anon.

• ywb

  SSC Enthusiast

  Points: 162

  More actions

  October 27, 2005 at 9:38 am

  #600620

  Hi,

  Thanks for the advice.

  Just to share that somebody gave me this link and it worked perfectly:

       Processing The Same Row More Than Once

       (http://www.sqldts.com/default.aspx?266)

  I have yet to try out your suggestion.

   

  Thanks,

  YWB.