How to combine multiple results sets into one single result set for further use in SSRS?

Question

Post reply

How to combine multiple results sets into one single result set for further use in SSRS?

jean-paul.accarie

Say Hey Kid

Points: 695
More actions
September 29, 2009 at 3:48 am

#385455

-- using SQL Server 2005, SSRS 2005
-- Request blow will generate mulitple result sets, Is-there any tips to produce only one result sets to be further used within SSRS?
-- Thanks for your help
------------------------------------
DECLARE @current_name VARCHAR(30)
DECLARE name_cursor CURSOR FOR
SELECT DISTINCT ResourceName
FROM MSP_EpmResource_UserView
ORDER BY MSP_EpmResource_UserView.ResourceName
BEGIN
OPEN name_cursor;
END
FETCH NEXT FROM name_cursor INTO @current_name
WHILE @@FETCH_STATUS = 0
BEGIN
-- Here I placed a rather complex SELECT FROM query which uses @current_name as parameter
-- SELECT FROM query returns 24 columns values
FETCH NEXT FROM name_cursor INTO @current_name;
END
CLOSE name_cursor;
DEALLOCATE name_cursor;

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

bendy1999 SSC Eights! Points: 932 More actions · Answer 1

Hi Jean-Paul,

Did you try to add something like this to your complex select statement?

Where column in ((select distinct ResourceName From MSP_EpmResource_UserView))

or you could add the distinct ResourceNames as separate Dataset to your report, use it as source for a Parameter and then use that parameter in your complex select statement.

You could then set the parameter to multi-value so that the user can control which resource-Names he retreives from the database

jean-paul.accarie Say Hey Kid Points: 695 More actions · Answer 2

Hi bendy-983487

Thanks for your reply

I fully understand your 2 proposals (which I actually use within other reports).

The point is that the "complex" query is very specific to a user (with user replacement with current name vallue when some "name" fields are empty).

That's why I was wondering how to otbtain the total single set as the addition of the different results sets (~70 in my specific case)

Is-there any tips such as

- to embed query into higher level query?

- to use a temporary table?

Any pointer (with samples if possible) is welcome

thanks for your help

bendy1999 SSC Eights! Points: 932 More actions · Answer 3

Hmm...

So when there is no Name returned in your first query, then your Complex one uses the one of the current user?

jean-paul.accarie Say Hey Kid Points: 695 More actions · Answer 4

To answer your question, Name will never be empty: in addition in my "complex" query I do specific things like ResourceName = ISNULL(table1.ResourceName, t .ResourceName) which can only be done on a per resourceName (this is why the CURSOR mechanism seems adequate)

My original question is really about possibility to "group" obtained results set into one single set to be later used within SSRS

Any tips?

Thanks for your time

bendy1999 SSC Eights! Points: 932 More actions · Answer 5

Quick-Shot: Try to wrap a " Select * from (yourqueries) as T " around your queries. Although i don't know if this works when using cursors.

jean-paul.accarie Say Hey Kid Points: 695 More actions · Answer 6

I did many tries without any succes:

- the "select * from (my queries) as T" still generate different result sets

- If I try to put "select * from" at the begining, ie before the DECLARE... this gives error messages like "Incorrect syntax near the keyword 'DECLARE'."

But indeed this would be perfect if we could write a "select * from" to include the CURSOR mechism which produces the multiple result sets

Any other tips to test?

What about a temporary table? Any code sample?

bendy1999 SSC Eights! Points: 932 More actions · Answer 7

Temporary tables are worth a try, hopefully it won't have the same restrictions like the "Select * from" approach.

I'm not so experienced in using cursors, thats why my suggestions might only help little.

Did you take a look at Common Table expressions? It lets you create recursive queries similar to your cursor, but returns a single dataset.

Here are some Infos

http://msdn.microsoft.com/en-us/library/ms190766.aspx

jean-paul.accarie Say Hey Kid Points: 695 More actions · Answer 8

I had a look on CTE but unfortunately I don't think this is adapted because the data processing I do are more sequential than recursive.

In the meantime I also tested without using CURSOR but with WHILE only: I also obtained different result sets!

I will try using temporary table...

I cannot imagine that there is no mean to easily put together (into one single result set) as a list the different result sets!

Any other idea is welcome

jean-paul.accarie Say Hey Kid Points: 695 More actions · Answer 9

Just to inform that I finally solve my issue using temporary table (keeping CURSOR and WHILE loop mechanism):

actually I am using "variable table" type of temporary table , steps are

1) "DECLARE variable table @tmpTable (my fields...)"

2) just above my complex query

"INSERT INTO @Tmp (SELECT my complex query...)"

3) At the end just perform just do "SELECT * FROM @tmpTable"

That's it...

Don't hesitate to comment if you think there need to take some precautions...

Bye

bendy1999 SSC Eights! Points: 932 More actions · Answer 10

bendy1999

SSC Eights!

Points: 932

October 2, 2009 at 12:26 am

#1060792

Hi Jean-Paul,

Good to hear that it worked out 🙂

cpicard Mr or Mrs. 500 Points: 563 More actions · Answer 11

Hi There,

I have the same problem as you did. I have a cursor that uses input @entity_sk to run a stored procedure. I have about 159 seperate datasets that are being returned and I am trying to combine them.

I followed your solution (creating a temp table to have information inputed into), but I do not know how to integrate the Insert @temptable with a stored procedure rather than a complex select statement.

Any suggestions??