Get data one by one and split using cursor or while loop

Question

Post reply

Get data one by one and split using cursor or while loop

spungus

SSC-Addicted

Points: 443
More actions
September 8, 2011 at 7:46 am

#99417

Hi to all,
can anyone help me regarding my problem.
here is it...
table name: table1
id prodname value split_value
1 prod1 1000 500
2 prod2 3000 1000
i have to get each record in table 1 and split that
base on split value.
sample output
id prodname orig_value per_run total_value no_run
1 prod1 1000 500 500 1
2 prod1 1000 500 1000 2
3 prod2 3000 1000 1000 1
4 prod2 3000 1000 2000 2
5 prod2 3000 1000 3000 3
I have a split code, the problem its only get one record.
thanks to all!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

Try this, assumes value is an exact multiple of split_value

DECLARE @table1 TABLE(id INT,prodname VARCHAR(10), value INT, split_value INT)

INSERT INTO @table1(id,prodname,value,split_value)

SELECT 1, 'prod1', 1000, 500 UNION ALL

SELECT 2, 'prod2', 3000, 1000;

SELECT a.id,

a.prodname,

a.value AS orig_value,

a.split_value AS per_run,

t.N * a.split_value AS total_value,

t.N AS no_run

FROM @table1 a

INNER JOIN dbo.Tally t ON t.N BETWEEN 1 AND a.value/a.split_value;

See here for information on building a tally table

http://www.sqlservercentral.com/articles/T-SQL/62867/

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

spungus SSC-Addicted Points: 443 More actions · Answer 2

Thanks for the quick answer Mark-101232.

I forgot to use Tally Table, which i already know! lol!

The article is very good...i just keep it in my mind that if i have to

use cursor or loop..there's Tally table to save me!

---

Thanks also for Forum Etiquette link..i just read it now.

Sorry my mistake.

spungus SSC-Addicted Points: 443 More actions · Answer 3

@Mark-101232

Hi! This topic is old, just want to add another question.

If it's not exact multiple of split_value, how can i get the remaining split id?

For example

DECLARE @table1 TABLE(id INT,prodname VARCHAR(10), value INT, split_value INT)

INSERT INTO @table1(id,prodname,value,split_value)

SELECT 1, 'prod1', 5000, 2000;

I'm currently trying to do this and i not able to produce an output that will have an split id

from 1 to 3.

Thanks!

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

metnik1981 (10/14/2011)
@Mark-101232
Hi! This topic is old, just want to add another question.
If it's not exact multiple of split_value, how can i get the remaining split id?
For example
DECLARE @table1 TABLE(id INT,prodname VARCHAR(10), value INT, split_value INT)
INSERT INTO @table1(id,prodname,value,split_value)
SELECT 1, 'prod1', 5000, 2000;
I'm currently trying to do this and i not able to produce an output that will have an split id
from 1 to 3.
Thanks!

DECLARE @table1 TABLE(id INT,prodname VARCHAR(10), value INT, split_value INT)

INSERT INTO @table1(id,prodname,value,split_value)

SELECT 1, 'prod1', 1000, 500 UNION ALL

SELECT 2, 'prod2', 3000, 1000 UNION ALL

SELECT 3, 'prod3', 5000, 2000;

--Use a real tally table for performance, I've added this to make it easier to execute

;WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),

t2 AS (SELECT 1 N FROM t1 x, t1 y),

t3 AS (SELECT 1 N FROM t2 x, t2 y),

t4 AS (SELECT 1 N FROM t3 x, t3 y),

tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS num

FROM t4 x, t4 y)

--Actual query

SELECT a.id,

a.prodname,

a.value AS orig_value,

a.split_value AS per_run,

t.num * a.split_value AS total_value,

t.num AS no_run

FROM @table1 a

INNER JOIN tally t ON t.num BETWEEN 1 AND CEILING((a.value * 1.0)/a.split_value);

--EDIT--

Sorry, my on the fly tally table won't work in SQL Server 2000, so as I said make sure you use a real one 🙂

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

spungus SSC-Addicted Points: 443 More actions · Answer 5

@cadavre

thanks! just using CEILING and some tweaks and it work!

right now,im working on the actual value of the quantity. 😀