• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 745
  • Last Modified:

How to split a single row into multiple rows based upon a number value

I have a quick question. I am trying to split a single row into multiple rows based upon a specific number value. I want the single row to be split every time it has 50 units. So if a row has 150 units in it, the row will be split 3 times, with 50 units as the value for each. Or if the row has 233 units, the row will be split 5 times, 4 rows with 50 units and one row with 33 units.

Example:

Start:

Carrier Units
CBA Carriers 150 units
ABC Carriers 233 units

End:

CBA Carriers 50 units
CBA Carriers 50 units
CBA Carriers 50 units
ABC Carriers 50 units
ABC Carriers 50 units
ABC Carriers 50 units
ABC Carriers 50 units
ABC Carriers 33 units
0
Kristie
Asked:
Kristie
1 Solution
 
TrixorCommented:
I wonder if this would help you:

DECLARE @rowsperpage INT

DECLARE @start INT

SET @start = 0
SET @rowsperpage = 50

SELECT * FROM
(
SELECT row_number() OVER (ORDER BY column) AS rownum, column2, column3, .... columnX
FROM   table) AS A
WHERE A.rownum BETWEEN (@start) AND (@start + @rowsperpage) 

Open in new window


(source: http://www.select-sql.com/mssql/how-to-make-limit-from-to-in-mssql-2005.html)
0
 
Ephraim WangoyaCommented:

try this
declare @Test Table(Carrier varchar(50), Units int)

declare @Result Table(Carrier varchar(50), Units int)

insert into @Test
select 'CBA Carriers', 175
union all
select 'ABC Carriers', 233

declare @Qty int, @SubQty int, @Carrier varchar(50)
declare Cr cursor for select Carrier, Units from @test

open Cr
fetch next from Cr 
into @Carrier, @Qty

while (@@FETCH_STATUS = 0)
begin
  while @Qty > 0
  begin 
    if @Qty > 50 
      set @SubQty = 50 
    else
      set @SubQty = @Qty 

    insert into @Result(Carrier, Units)
    values (@Carrier, @SubQty )
    
    set @Qty = @Qty - 50
  end;
  fetch next from Cr 
  into @Carrier, @Qty 
end
close Cr;
deallocate Cr;

select * from @Result

Open in new window

0
 
KristieAuthor Commented:
the above is not what I am looking for.  Please review my questions and table example.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
Anthony PerkinsCommented:
Please post your table schema and a SQL Script to populate the data.
0
 
KristieAuthor Commented:
ewangoya query works but takes a very long time to loop through all the records.  
0
 
GhunaimaCommented:
Try attached SQL sql1.sql
0
 
Anthony PerkinsCommented:
>> but takes a very long time to loop through all the records.<<
If you are still using SQL Server 2000, you may not have much choice.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Tackle projects and never again get stuck behind a technical roadblock.
Join Now