Splitting a row into multiple rows based on an attribute

• pYak

  Ten Centuries

  Points: 1027

  More actions

  February 23, 2010 at 12:35 am

  #156707

  Hi All

  I have a seemingly simple TSQL problem which is proving more challenging than I anticipated.

  I hoping someone can advise if there is a set based solution for it.

  Essentially I have a table which needs to be split into multiple rows based on an attribute

  (tran_count in the #tran table below). The end representation will need to look like

  the output from #tran2. Any suggestions would be appreciated.

  --drop table #tran

  create table #tran (id int identity(1,1), amount decimal(10,2), tran_count int);

  insert into #tran values (10.5, 2)

  insert into #tran values (12, 3);

  insert into #tran values (22, 4);

  insert into #tran values (11.50, 1);

  select * from #tran;

  --drop table #tran2

  create table #tran2 (id int identity(1,1), original_id int, amount decimal(10,2));

  insert into #tran2 values (1, 5.25);

  insert into #tran2 values (1, 5.25);

  insert into #tran2 values (2, 4);

  insert into #tran2 values (2, 4);

  insert into #tran2 values (2, 4);

  insert into #tran2 values (3, 5.5);

  insert into #tran2 values (3, 5.5);

  insert into #tran2 values (3, 5.5);

  insert into #tran2 values (3, 5.5);

  insert into #tran2 values (11.5, 1);

  select * from #tran2;

• LutzM

  SSC Guru

  Points: 107049

  More actions

  February 23, 2010 at 1:00 am

  #1123461

  Following please find an example of how I would do it.

  Instead of creating the numbers table on the fly I usually would use my Tally table. If you don't have one by now you should follow the related link in my signature how to build one and to learn more about what it's useful for.

  SELECT id,(1.0* amount)/tran_count AS amount

  FROM #TRAN

  CROSS APPLY

  (

  SELECT 1 AS n UNION ALL

  SELECT 2 UNION ALL

  SELECT 3 UNION ALL

  SELECT 4 UNION ALL

  SELECT 5 UNION ALL

  SELECT 6

  ) numbers

  WHERE n<=tran_count

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• pYak

  Ten Centuries

  Points: 1027

  More actions

  February 23, 2010 at 1:11 am

  #1123464

  Thanks Lutz!

  That should get me going fine....

  I think CROSS APPLY is going to be my new best friend. 🙂

  Cheers