Selecting multiple tables from single table by distinct column value in stored procedure

Question

I have a bit of a conundrum I need to deal with, and I'm trying to find a good solution to it. I'm using MS SQL server 2005, and given a table where a uniqueidentifier column contains a data key to other information on the server. What I need to do is select every row in this datatable (sproc from ASP.NET), but separated into tables based on this one column. For example, from

------------------------------------
MyID    |  MyOtherData
------------------------------------
 4         asdf
 4         test
 3         qwerty
 2         morestuff

I'd want the following three tables returned:

------------------------------------
MyID    |  MyOtherData
------------------------------------
 4         asdf
 4         test

------------------------------------
MyID    |  MyOtherData
------------------------------------
 3         qwerty

------------------------------------
MyID    |  MyOtherData
------------------------------------
 2         morestuff

How would be the best way for me to go around this, if I don't know how many unique MyID values there will be ahead of time?

Answer 1

The only thing that comes to mind here, and it isn't a very good idea....but would be to create a Cursor, that works on a query that does

SELECT DISTINCT MyId FROM MyTable

Then, each iteration of the cursor does the respective select...

Answer 2

I think you should try and rethink your approach as I don't think it'll be very scalable and I think trying to find a way to achieve this will not be worthwhile in the long run.

Instead, could you not return a single resultset, ordered by MyID and then handle that resultset differently in .NET?

Answer 3

Your best bet is not to do this in the Proc at all, but in the client code.

1. Write a query/proc that returns the distinct MyID values (SELECT Distinct MYID FROM MyTable)
2. In your app, loop through each value selected and call another proc that takes a MyID value and returns a recordset for that value. (SELECT MyOtherData from MyTable Where MyID=?)

Answer 4

You could create a loop structure that iterates through a subset of the main table's IDs to spit out a separate result set for each set of tables. This is ugly but hopefully this is just some ad-hoc thing that you're wanting to get out of the way and hope to never have to deal with again.

DECLARE @Entities TABLE
(
     ID int IDENTITY (1, 1) PRIMARY KEY NOT NULL,
     MyID int,
     MyOtherData varchar(100)
)

INSERT INTO @Entities (MyID, MyOtherData)
SELECT MyID, MyOtherData
FROM TheMainTable
ORDER BY MyID, MyOtherData

DECLARE @Count int
DECLARE @Max int

SET @Count = 1
SELECT @Max = COUNT(ID) FROM @Entities

DECLARE @MyID_Current int

WHILE (@Count <= @Max)
BEGIN

     SELECT @MyID_Current = MyID
     FROM @Entities
     WHERE ID = @Count

     -- Output each individual result set.
     SELECT MyID, MyOtherData
     FROM @Entities
     WHERE MyID = @MyID_Current

     SET @Count = @Count + 1

END

For something even uglier, you could instead of just churning out the separate result sets CREATE or SELECT INTO a new, uniquely-named table in your database at each iteration, and insert a particular MyID's records into that table.