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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
52326 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
52326 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  
 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.33 seconds. Powered By: Snitz Forums 2000