Create Table From Division of Two Columns In Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Create Table From Division of Two Columns In Table

Author

Topic

asford3188
Starting Member

2 Posts

Posted - 2013-10-25 : 17:20:05

I’m not sure how to phase my question, which is probably why I couldn’t find a good answer on Google. I want to use a couple numbers pulled from columns in a table that I already have, and create another table with a number of records equal to one of those numbers divided by the other. The best way to explain is probably by example, so here goes:

This is the table that I already have:
Product|UnitsPerCase|TotalUnits
ABC|4|20
DEF|2|16
GHI|8|16

This is the table I want to create programmatically in SQL Server, based on the table above:
Product|UnitsPerCase|CaseNumber
ABC|4|1
ABC|4|2
ABC|4|3
ABC|4|4
ABC|4|5
DEF|2|1
DEF|2|2
DEF|2|3
DEF|2|4
DEF|2|5
DEF|2|6
DEF|2|7
DEF|2|8
GHI|8|1
GHI|8|2

I don’t even have a guess as to how to do this. Any help is appreciated!

chadmat
The Chadinator

1974 Posts

Posted - 2013-10-25 : 18:35:16

This may not be the most efficient way to do this, but it was the only thing that came to mind. I hate suggesting cursors, but here you go:

use tempdb
go

create table test(c1 varchar(3),c2 int,c3 int)

insert into test values('ABC', 4, 20)
insert into test values('DEF', 2, 16)
insert into test values('GHI', 8, 16)

SELECT * INTO NewTest FROM test where 1=0

DECLARE @c1 varchar(3)
DECLARE @c2 int
DECLARE @c3 int

DECLARE mycur cursor
for select c1, c2, c3 from test

open mycur
fetch next from mycur into @c1, @c2, @c3

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @i int

SELECT @i = @c3/@c2

WHILE (@i > 0)
BEGIN
INSERT INTO NewTest Values(@c1, @c2, @i)

SET @i -= 1

END

FETCH NEXT From mycur
into @c1, @c2, @c3

END

SELECT * FROM NewTest
ORDER BY c1 asc, c3 asc

CLOSE mycur
deallocate mycur

DROP table test
DROP table NewTest

-Chad
Microsoft Certified Master SQL Server 2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-26 : 04:58:01

You can just do this without using a cursor


;With Numbers
AS
(
SELECT 1 AS N
UNION ALL
SELECT N + 1
FROM Numbers
WHERE N + 1 <=1000
)


SELECT t.Product, t.UnitsPerCase, ROW_NUMBER() OVER (PARTITION BY t.Product ORDER BY t.Product) AS CaseNumber
FROM Table t
CROSS JOIN Numbers n
WHERE N BETWEEN 1 AND (TotalUnits/UnitsPerCase)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

asford3188
Starting Member

2 Posts

Posted - 2013-10-28 : 10:26:09

quote:
Originally posted by visakh16

You can just do this without using a cursor

;With Numbers
AS
(
SELECT 1 AS N
UNION ALL
SELECT N + 1
FROM Numbers
WHERE N + 1 <=1000
)

SELECT t.Product, t.UnitsPerCase, ROW_NUMBER() OVER (PARTITION BY t.Product ORDER BY t.Product) AS CaseNumber
FROM Table t
CROSS JOIN Numbers n
WHERE N BETWEEN 1 AND (TotalUnits/UnitsPerCase)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

That is excellent. Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-28 : 13:20:29

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources