Multiple Resultset against One Query

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 2000 Forums

Transact-SQL (2000)

Multiple Resultset against One Query

Author

Topic

real_pearl
Posting Yak Master

106 Posts

Posted - 2004-11-02 : 02:52:54

I want to know is it possible to get as many result sets as many groups exists. e.g.,

SELECT order_date, SUM(quantity) as quantity
FROM orders
WHERE order_date BETWEEN from_date AND to_date
GROUP BY order_date

if there are 4 groups then it may return 4 result sets.

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-11-02 : 03:11:05

don't think so.
either do in presentation layer.
or if need to be done in a stored proc,
- load the results in a temp table.
- loop thru the temp table and SELECT each (single) record (by cursor or otherwise)

Hemanth Gorijala
Exchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each.

real_pearl
Posting Yak Master

106 Posts

Posted - 2004-11-02 : 03:30:06

Yeah know it would be much easier in the middle tier. But I just wanted to know is there any way for doing this.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-02 : 04:17:32

1. get the group by criteria, order_date in this case
2. use the group to query through the data

--------------------
keeping it simple...

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-02 : 04:26:52

[code]DECLARE @order_date DATETIME
SET @order_date = ( SELECT MIN(order_date) FROM orders
WHERE order_date BETWEEN from_date AND to_date )

WHILE @order_date IS NOT NULL
BEGIN
SELECT @order_date, SUM(quantity) as quantity
FROM orders
WHERE order_date = @order_date
SET @order_date = ( SELECT MIN(order_date) FROM orders
WHERE order_date > @order_date
AND order_date BETWEEN from_date AND to_date )
END[/code]

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources