newbie: returning multiple top 30 items
-
Hi, I'm trying to simplify a stored procedure but have got stuck. I have a table that contains a list of 'items' and another that contains a list of 'options' associated with each 'item'. I am currently executing the stored procedure for each item one by one. Obviously this is a bad way, as it hammers the server. Is there a way to return the 30 options as 30 columns for each item/row? Hope that makes sense.. Thanks in advance, Phil Hodges
"Rules are for the obedience of fools and the guidance of wise men"
-
Hi, I'm trying to simplify a stored procedure but have got stuck. I have a table that contains a list of 'items' and another that contains a list of 'options' associated with each 'item'. I am currently executing the stored procedure for each item one by one. Obviously this is a bad way, as it hammers the server. Is there a way to return the 30 options as 30 columns for each item/row? Hope that makes sense.. Thanks in advance, Phil Hodges
"Rules are for the obedience of fools and the guidance of wise men"
Phillip Hodges wrote:
Is there a way to return the 30 options as 30 columns for each item/row?
Yes. But you haven't mentioned which database you are using so an example would be a bit of a stab in the dark hoping that you might be using the same database. If you are using SQL Server 2005 you might want to look up the documentation for PIVOT
Upcoming events: * Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ... "I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless." My website
-
Hi, I'm trying to simplify a stored procedure but have got stuck. I have a table that contains a list of 'items' and another that contains a list of 'options' associated with each 'item'. I am currently executing the stored procedure for each item one by one. Obviously this is a bad way, as it hammers the server. Is there a way to return the 30 options as 30 columns for each item/row? Hope that makes sense.. Thanks in advance, Phil Hodges
"Rules are for the obedience of fools and the guidance of wise men"
It's a really bad idea to try to "flatten" your structure this way. There are many ways that you could retrieve the data, but one approach would be to retrieve the data in the items information, and then perform a separate select inside the same procedure to retrieve the options based on the same criteria as you used to retrieve the item. Then, all you need to do is (assuming you are using .NET), retrieve from the first table into a DataAdapter and retrieve the second table into the same DataAdapter as a new DataTable. In the data adapter, you would then create a relationship between the first DataTable and the second DataTable. That's it. Search Google for creating master detail in ADO.NET.
Deja View - the feeling that you've seen this post before.
-
Hi, I'm trying to simplify a stored procedure but have got stuck. I have a table that contains a list of 'items' and another that contains a list of 'options' associated with each 'item'. I am currently executing the stored procedure for each item one by one. Obviously this is a bad way, as it hammers the server. Is there a way to return the 30 options as 30 columns for each item/row? Hope that makes sense.. Thanks in advance, Phil Hodges
"Rules are for the obedience of fools and the guidance of wise men"
Phillip Hodges wrote:
Hope that makes sense..
Uh, well maybe, sort of, a little... If the 30 options are fixed (constants), then maybe you do want a PIVOT table like Colin suggested. Otherwise, I am confused about what you want. Perhaps an example would help. Or, if you already have what you need, well that'll work too. David