Creating Records based on Quant Field
-
Hello All, Sorry to bother you but I am hitting up against a wall trying to figure something out so I am turning to you for assistance (if possible.). I have a table of let's say widgets. And for each entry there is a Quantity column. What I am needing to do is to write a Store Procedure for SQL Server that will pull all widgets for a certain customer and create a record in another table for each widget. No problem there. But I need to create a record for each widget based on it's quantity column. So for example customer A brought 3 widget items: Widget1 5 pieces, Widget2 1 piece, and Widget3 3 pieces. What I need to end up with is a new table with 9 entries. 1 for each widget piece. Again I am sorry to bother you guys, but I am really at a lost. I thank you in advance for any help you can direct me with. Fred
-
Hello All, Sorry to bother you but I am hitting up against a wall trying to figure something out so I am turning to you for assistance (if possible.). I have a table of let's say widgets. And for each entry there is a Quantity column. What I am needing to do is to write a Store Procedure for SQL Server that will pull all widgets for a certain customer and create a record in another table for each widget. No problem there. But I need to create a record for each widget based on it's quantity column. So for example customer A brought 3 widget items: Widget1 5 pieces, Widget2 1 piece, and Widget3 3 pieces. What I need to end up with is a new table with 9 entries. 1 for each widget piece. Again I am sorry to bother you guys, but I am really at a lost. I thank you in advance for any help you can direct me with. Fred
So break it up into smaller bits. open management studio and connect to the database - this is where you should be doing your test/creating
select *
From Widgets
Where CustomerID = @CustIDThat should get you the list of widgets for the customer Now you want to count the record for each widget type
select count(*),WidgetTypeID
From Widgets
Where CustomerID = @CustID
GroupBy WidgetTypeIDNow add the insert statement around the select
Insert OtherTable(CustID,WidgetTypeID, Qty)
select CustID, WidgetTypeID, count(*)
From Widgets
Where CustomerID = @CustID
GroupBy CustID, WidgetTypeIDHere endeth the lesson
Never underestimate the power of human stupidity RAH
-
Hello All, Sorry to bother you but I am hitting up against a wall trying to figure something out so I am turning to you for assistance (if possible.). I have a table of let's say widgets. And for each entry there is a Quantity column. What I am needing to do is to write a Store Procedure for SQL Server that will pull all widgets for a certain customer and create a record in another table for each widget. No problem there. But I need to create a record for each widget based on it's quantity column. So for example customer A brought 3 widget items: Widget1 5 pieces, Widget2 1 piece, and Widget3 3 pieces. What I need to end up with is a new table with 9 entries. 1 for each widget piece. Again I am sorry to bother you guys, but I am really at a lost. I thank you in advance for any help you can direct me with. Fred
Are you looking for this
declare @tblCustomers table(CustID int,CustomerName varchar(50))
declare @tblWidgets table(CustID int,WidgetTypeID int, Qty int)
declare @tblTarget table(CustID int,WidgetTypeID int, Qty int)
insert into @tblCustomers values(101,'Cust1'),(102,'Cust2'),(103,'Cust3')
insert into @tblWidgets values(101,1,5),(101,2,1),(101,3,3),(102,10,5),(103,4,1)--Program starts
insert into @tblTarget
select CustID,WidgetTypeID,Qty
from
(
select w.CustID,w.WidgetTypeID,w.Qty from @tblWidgets w
inner join @tblCustomers c
on c.CustID = w.CustID
)X where X.CustID =101
select * from @tblTargetThe output being
CustID WidgetTypeID Qty
101 1 5
101 2 1
101 3 3:)
Niladri Biswas
-
Hello All, Sorry to bother you but I am hitting up against a wall trying to figure something out so I am turning to you for assistance (if possible.). I have a table of let's say widgets. And for each entry there is a Quantity column. What I am needing to do is to write a Store Procedure for SQL Server that will pull all widgets for a certain customer and create a record in another table for each widget. No problem there. But I need to create a record for each widget based on it's quantity column. So for example customer A brought 3 widget items: Widget1 5 pieces, Widget2 1 piece, and Widget3 3 pieces. What I need to end up with is a new table with 9 entries. 1 for each widget piece. Again I am sorry to bother you guys, but I am really at a lost. I thank you in advance for any help you can direct me with. Fred
ffowler wrote:
So for example customer A brought 3 widget items: Widget1 5 pieces, Widget2 1 piece, and Widget3 3 pieces. What I need to end up with is a new table with 9 entries. 1 for each widget piece.
9 entries? How do you get 9 entries from this? I can't quite follow that logic.
-
So break it up into smaller bits. open management studio and connect to the database - this is where you should be doing your test/creating
select *
From Widgets
Where CustomerID = @CustIDThat should get you the list of widgets for the customer Now you want to count the record for each widget type
select count(*),WidgetTypeID
From Widgets
Where CustomerID = @CustID
GroupBy WidgetTypeIDNow add the insert statement around the select
Insert OtherTable(CustID,WidgetTypeID, Qty)
select CustID, WidgetTypeID, count(*)
From Widgets
Where CustomerID = @CustID
GroupBy CustID, WidgetTypeIDHere endeth the lesson
Never underestimate the power of human stupidity RAH
According to the OP, this part of it is not a problem. There seems to be some sort of additional requirement which is the sticking point. I'm not entirely sure I understand the problem, but it seems to involve creating more rows in the target table than exist in the source table. 1 customer with 3 widgets should give 9 rows. That's the bit I don't quite get.
-
According to the OP, this part of it is not a problem. There seems to be some sort of additional requirement which is the sticking point. I'm not entirely sure I understand the problem, but it seems to involve creating more rows in the target table than exist in the source table. 1 customer with 3 widgets should give 9 rows. That's the bit I don't quite get.
Seems like we are all struggling with this convoluted logic. I'll wait till the OP comes back with some clarification
Never underestimate the power of human stupidity RAH
-
ffowler wrote:
So for example customer A brought 3 widget items: Widget1 5 pieces, Widget2 1 piece, and Widget3 3 pieces. What I need to end up with is a new table with 9 entries. 1 for each widget piece.
9 entries? How do you get 9 entries from this? I can't quite follow that logic.
Flash of insight. The number of rows to create in the target table depends on the quantity in the source table. So, customer A buys 3 widget items, Widget1 5 pieces, Widget2 1 piece and Widget3 3 pieces, what you want in the final result table is:
CustId WidgetID
A W1
A W1
A W1
A W1
A W1
A W2
A W3
A W3
A W3In other words, 9 rows. Is that right?
-
Flash of insight. The number of rows to create in the target table depends on the quantity in the source table. So, customer A buys 3 widget items, Widget1 5 pieces, Widget2 1 piece and Widget3 3 pieces, what you want in the final result table is:
CustId WidgetID
A W1
A W1
A W1
A W1
A W1
A W2
A W3
A W3
A W3In other words, 9 rows. Is that right?
-
That is correct David. I need a row created based on what is in the Quantity field of the original table. You have outlined it perfectly. Sorry I didn't make it clearer to you others originially. Now, can it be done, or do I have do something externally?
Others may have a better suggestion, but I would select customer, widget, quantity and then use a cursor to fetch each row in turn. Then, for each row I would have a loop which repeats q times (where q is the quantity value for that row) and do an insert into the target table inside the loop. Does that make sense? I'm not sure how you are planning to make each of the new rows unique in the target table, but at the simplest you could stick an identity column on there to handle the uniqueness. It seems a slightly odd requirement but I guess there is a reason behind it. Sorry, no code because I don't use cursors that often, so I'm a bit rusty on the exact syntax. But Google is your friend when it comes to things like that.
-
Others may have a better suggestion, but I would select customer, widget, quantity and then use a cursor to fetch each row in turn. Then, for each row I would have a loop which repeats q times (where q is the quantity value for that row) and do an insert into the target table inside the loop. Does that make sense? I'm not sure how you are planning to make each of the new rows unique in the target table, but at the simplest you could stick an identity column on there to handle the uniqueness. It seems a slightly odd requirement but I guess there is a reason behind it. Sorry, no code because I don't use cursors that often, so I'm a bit rusty on the exact syntax. But Google is your friend when it comes to things like that.
David, I am required to create a report whereby each item quantity has it's own line (for jotting notes or whatever next to each. I love my Sales Dept!!!) In any event, I see where you are going with that, but I won't use a cursor. There is a niffty loop that I found that takes the place of using cursors which I have found is alot lighter. I think that is a plan and I'll give it a go. Thanks to all of you for your time with my issue, which is certainly not yours. I appreciate it.