SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Create Table From Division of Two Columns In Table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

asford3188
Starting Member

USA
2 Posts

Posted - 10/25/2013 :  17:20:05  Show Profile  Reply with Quote
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!

Edited by - asford3188 on 10/25/2013 17:28:11

chadmat
The Chadinator

USA
1974 Posts

Posted - 10/25/2013 :  18:35:16  Show Profile  Visit chadmat's Homepage  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/26/2013 :  04:58:01  Show Profile  Reply with Quote
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
Go to Top of Page

asford3188
Starting Member

USA
2 Posts

Posted - 10/28/2013 :  10:26:09  Show Profile  Reply with Quote
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!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/28/2013 :  13:20:29  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000