February 28, 2006 at 4:30 pm
Hi,
I have a product table (productid, categoryid, productname, price). I want to group the products by categoryid and return each group as a seperate resultset. Can any one tell me whats the easy way to do this?
The categoryid value is not constant, administrators can add/edit/delete categories stored in the category table. So I cannot I have a list of select statements, like this
SELECT * FROM products WHERE categoryid = 1
SELECT * FROM products WHERE categoryid = 2
...
I can only think of this. Get the categoryvalues in a temp table. In a loop, get each categoryid from the table and do a select, like this
SELECT * FROM products WHERE categoryid = @categoryid
Any suggestions?
Thanks.
February 28, 2006 at 4:50 pm
What do you mean "return each group as a seperate resultset"?
Return to where? And from where?
_____________
Code for TallyGenerator
February 28, 2006 at 5:04 pm
Like in a seperate select statement. Like
SELECT * FROM products WHERE categoryid = 1
SELECT * FROM products WHERE categoryid = 2
To use it in ASP.Net like this, DataReader.NextResult().
February 28, 2006 at 6:15 pm
If you really need it this way it must be WHILE loop or a cursor.
_____________
Code for TallyGenerator
March 1, 2006 at 4:50 am
Hi,
Is this is your Answer ?
select * from products where category_id
in(select category_id from products)
Regards
AMIT GUPTA
March 1, 2006 at 7:27 am
My suggestion is this.
Create a Stored Procedure
CREATE PROC Get_Products_In_Catergory
@CategoryID as int
AS
SET NOCOUNT ON
SELECT
* -- You should list the columns you need as opposed to using *,
-- makes things more flexible with fewer consequences if changes occurr.
FROM
dbo.products -- You should qualify schema for performance reasons
WHERE
categoryid = @CategoryID
GO
Then in your application create a loop to pull the data for each categoryid in the .NET application. This is much simplier and easier to troubleshoot.
I can get you an example of what I mean if you need.
March 1, 2006 at 8:43 am
No thanks. This should do.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply