Return multiple table using one select query

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

, +

I have a table with data as follows.

Name     Size
----------------
A          1
B          2
C          1
D          1
E          3
F          2

I have to write an SQL (sql server) which will return 3(as there  are 3 types of sizes are in the table) tables. The number of produced table can be increased by inserting new size data in the main table.

The result of the required SQL should be as follows:-
Name     Size
----------------
A          1
C          1
D          1

Name     Size
----------------
B          2
F          2

Name     Size
----------------
E          3

Is there any easy way to produce the result without using any loop in the SQL?

Posted 26-Jun-12 4:26am

JakirBB

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tim Corey · Accepted Answer · 2012-06-26T04:31:00

Solution 1

If you want three resultsets, then the answer is no. You would need a loop to do that (unless you hard-coded the statements, which would be worse). However, I don't see the business case for doing something like this. I would recommend returning one resultset and then break it apart in code (if you need to).

If you really needed three (or more) resultsets to be returned to your code, you could also create a stored procedure that takes in the Size you want and returns that resultset. That way you could be more flexible in how you called it and yet it would still handle the multiple cases.

These are just some ideas to refactor your design. In the end, the basic answer to your question is no, you cannot do that without a loop.

Posted 26-Jun-12 4:31am

Tim Corey

Comments

JakirBB 27-Jun-12 2:22am

Thanks, Tim Corey for your quick response. I agree with you. But one question, as I have two options - first is, generating one result set and use it in code [probably to generate other result sets], and other is using loop in SQL and generate multiple result sets. But my question is which one is better?

Thanks again for your quick reply.

Tim Corey 27-Jun-12 8:43am

I can give my general opinion, but some of this comes down to environment. In general, I like to let SQL do what it does best (process data) and I let my application do what it does best (interpret/display/capture data). That means I would probably create one stored procedure on the server that returned one list at a time. I would then probably call it three times. Three calls isn't ideal, but it would allow me to be more flexible in how I operated. My next choice would be the one stored procedure that looped through the items and returned multiple resultsets. As in any situation, though, you need to take your environment into account. Is your SQL server overloaded? If so, maybe you should process the data at the client. Is your front-end a bit slow? Then do all you can on the server before you send the data down. In the end, test, test, test.