spliting columns data

• asranantha

  SSCarpal Tunnel

  Points: 4246

  More actions

  August 6, 2011 at 10:32 am

  #149566

  i have a table called products with the values like

  ProductId ProductName

  10 A

  20 D,E,F,G

  30 B,C

  40 H,I,J

  I need to display each productid's with

  ProductId ProductName

  10 A

  20 D

  20 E

  20 F

  20 G

  30 B

  30 C

  40 H

  40 I

  40 J

  by useing ssis

  to solve it

• Jeff Moden

  SSC Guru

  Points: 1004405

  More actions

  August 6, 2011 at 2:11 pm

  #1364818

  Please see the first link in my signature line below for how to get quicker and coded answers for future posts.

  This is the way you should post data for problems you may post in the future.

  --===== Conditionally drop the test table to make reruns in SSMS easier.

  -- This is NOT a part of the solution.

  IF OBJECT_ID('TempDB..#Products','U') IS NOT NULL

  DROP TABLE #Products

  ;

  --===== Create the test table.

  -- Again, this is NOT a part of the solution.

  CREATE TABLE #Products

  (

  ProductID INT,

  ProductName VARCHAR(100)

  )

  ;

  --===== Populate the test table with test data from the post.

  -- Again, this is NOT a part of the solution.

  INSERT INTO #Products

  (ProductID, ProductName)

  SELECT 10,'A' UNION ALL

  SELECT 20,'D,E,F,G' UNION ALL

  SELECT 30,'B,C' UNION ALL

  SELECT 40,'H,I,J'

  ;

  Next, load the function that I've attached to this post. If you want to know how it works, refer to the URL that is included in the comments of that function.

  Finally, once you've loaded the function, the problem becomes child's play...

  --===== Now, solve the problem with some very simple code.

  SELECT product.ProductID,

  ProductName = split.ITEM

  FROM #Products product

  CROSS APPLY dbo.DelimitedSplit8K(product.ProductName,',') split

  ;

  I'll leave it to you to incorporate the code above into SSIS because I don't use SSIS for ETL (or anything else for that matter). 😉 It should be relatively easy, though.

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