Combine rows returned from within a Cursor loop into one Result

• QuietCoder

  SSChasing Mays

  Points: 621

  More actions

  April 25, 2018 at 11:05 am

  #344854

  Is it possible to combine the Rows returned from a SQL running within a Cursor?

  DECLARE sla_cursor CURSOR FOR
  select Name from People
  OPEN sla_cursor

  FETCH NEXT FROM sla_cursor INTO @name

  WHILE @@FETCH_STATUS = 0
  BEGIN  
       select * from PeopleDetail where Name = @name
      
       FETCH NEXT FROM sla_cursor INTO @name
  END

  This would give one result set per Loop when I run the stored proc...

  Name Age Country
  Ana 21 US

  Name Age Country
  Mary 31 US

  Name Age Country
  John 21 US

  I would like to combine the result into one grid so that I can display it on a SSRS report

  Name Age Country
  Ana 21 US
  Mary 31 US
  John 21 US

  Any ideas?

• ScottPletcher

  SSC Guru

  Points: 100947

  More actions

  April 25, 2018 at 11:11 am

  #1988231

  
SELECT *
FROM PeopleDetail PD
WHERE PD.Name IN (
    SELECT Name
    FROM People
)

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

• QuietCoder

  SSChasing Mays

  Points: 621

  More actions

  April 25, 2018 at 11:29 am

  #1988232

  ScottPletcher - Wednesday, April 25, 2018 11:11 AM

  
  SELECT *
  FROM PeopleDetail PD
  WHERE PD.Name IN (
      SELECT Name
      FROM People
  )

  Scott,

  Thanks for looking into this. What I have posted is a very simplistic example of the problem I am trying to solve. The cursor and the SQL running within is much more complex and would not fit into the solution above.

  I am just trying to see if there is a way to do what I require

  Thanks

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 25, 2018 at 11:56 am

  #1988234

  QuietCoder - Wednesday, April 25, 2018 11:29 AM

  ScottPletcher - Wednesday, April 25, 2018 11:11 AM

  
  SELECT *
  FROM PeopleDetail PD
  WHERE PD.Name IN (
      SELECT Name
      FROM People
  )

  Scott,

  Thanks for looking into this. What I have posted is a very simplistic example of the problem I am trying to solve. The cursor and the SQL running within is much more complex and would not fit into the solution above.

  I am just trying to see if there is a way to do what I require

  Thanks

  Since you are the only one that can see the code, we have no idea if the cursor can be eliminated.  Just a guess, it probably can.  If you insist on keeping the cursor (or at least not seeing if it can be eliminated) just insert the results of each run of the code inside the cursor into a temporary table and then select the data from the table after you exit the cursor.

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  April 25, 2018 at 12:14 pm

  #1988239

  Is this what you mean:
  
DECLARE @t table(name varchar(20), age tinyint, country varchar(20));
SET NOCOUNT ON;
INSERT @t ( name, age, country )
VALUES ( 'Ana', 21, 'US' )
     , ( 'Mary', 31, 'US' ) 
     , ( 'John', 21, 'US' ) ;
DECLARE @s-2 varchar(max) = '';
SELECT @s-2 += name+' '+Cast(age AS varchar(3))+' '+country+Char(10)
FROM @t t;
PRINT @s-2;

  Ana 21 US
  Mary 31 US
  John 21 US

• QuietCoder

  SSChasing Mays

  Points: 621

  More actions

  April 25, 2018 at 12:34 pm

  #1988244

  Lynn Pettis - Wednesday, April 25, 2018 11:56 AM

  QuietCoder - Wednesday, April 25, 2018 11:29 AM

  ScottPletcher - Wednesday, April 25, 2018 11:11 AM

  
  SELECT *
  FROM PeopleDetail PD
  WHERE PD.Name IN (
      SELECT Name
      FROM People
  )

  Scott,

  Thanks for looking into this. What I have posted is a very simplistic example of the problem I am trying to solve. The cursor and the SQL running within is much more complex and would not fit into the solution above.

  I am just trying to see if there is a way to do what I require

  Thanks

  Since you are the only one that can see the code, we have no idea if the cursor can be eliminated.  Just a guess, it probably can.  If you insist on keeping the cursor (or at least not seeing if it can be eliminated) just insert the results of each run of the code inside the cursor into a temporary table and then select the data from the table after you exit the cursor.

  Thanks Lynn! I think temp table is the way to go for me

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 25, 2018 at 12:39 pm

  #1988245

  QuietCoder - Wednesday, April 25, 2018 12:34 PM

  Lynn Pettis - Wednesday, April 25, 2018 11:56 AM

  QuietCoder - Wednesday, April 25, 2018 11:29 AM

  ScottPletcher - Wednesday, April 25, 2018 11:11 AM

  
  SELECT *
  FROM PeopleDetail PD
  WHERE PD.Name IN (
      SELECT Name
      FROM People
  )

  Scott,

  Thanks for looking into this. What I have posted is a very simplistic example of the problem I am trying to solve. The cursor and the SQL running within is much more complex and would not fit into the solution above.

  I am just trying to see if there is a way to do what I require

  Thanks

  Since you are the only one that can see the code, we have no idea if the cursor can be eliminated.  Just a guess, it probably can.  If you insist on keeping the cursor (or at least not seeing if it can be eliminated) just insert the results of each run of the code inside the cursor into a temporary table and then select the data from the table after you exit the cursor.

  Thanks Lynn! I think temp table is the way to go for me

  Actually, the way to go would be to try and eliminate the cursor if it can be eliminated.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 25, 2018 at 12:55 pm

  #1988250

  QuietCoder - Wednesday, April 25, 2018 12:34 PM

  Lynn Pettis - Wednesday, April 25, 2018 11:56 AM

  QuietCoder - Wednesday, April 25, 2018 11:29 AM

  ScottPletcher - Wednesday, April 25, 2018 11:11 AM

  
  SELECT *
  FROM PeopleDetail PD
  WHERE PD.Name IN (
      SELECT Name
      FROM People
  )

  Scott,

  Thanks for looking into this. What I have posted is a very simplistic example of the problem I am trying to solve. The cursor and the SQL running within is much more complex and would not fit into the solution above.

  I am just trying to see if there is a way to do what I require

  Thanks

  Since you are the only one that can see the code, we have no idea if the cursor can be eliminated.  Just a guess, it probably can.  If you insist on keeping the cursor (or at least not seeing if it can be eliminated) just insert the results of each run of the code inside the cursor into a temporary table and then select the data from the table after you exit the cursor.

  Thanks Lynn! I think temp table is the way to go for me

  I am working on a presentation to eliminate cursors.  If you would like, PM me the code and we will see what happens.

• QuietCoder

  SSChasing Mays

  Points: 621

  More actions

  April 30, 2018 at 2:37 pm

  #1988702

  Lynn Pettis - Wednesday, April 25, 2018 12:55 PM

  QuietCoder - Wednesday, April 25, 2018 12:34 PM

  Lynn Pettis - Wednesday, April 25, 2018 11:56 AM

  QuietCoder - Wednesday, April 25, 2018 11:29 AM

  ScottPletcher - Wednesday, April 25, 2018 11:11 AM

  
  SELECT *
  FROM PeopleDetail PD
  WHERE PD.Name IN (
      SELECT Name
      FROM People
  )

  Scott,

  Thanks for looking into this. What I have posted is a very simplistic example of the problem I am trying to solve. The cursor and the SQL running within is much more complex and would not fit into the solution above.

  I am just trying to see if there is a way to do what I require

  Thanks

  Since you are the only one that can see the code, we have no idea if the cursor can be eliminated.  Just a guess, it probably can.  If you insist on keeping the cursor (or at least not seeing if it can be eliminated) just insert the results of each run of the code inside the cursor into a temporary table and then select the data from the table after you exit the cursor.

  Thanks Lynn! I think temp table is the way to go for me

  I am working on a presentation to eliminate cursors.  If you would like, PM me the code and we will see what happens.

  Lynn,

  Thank you for pushing me to eliminate the cursor. It did not seem possible when I wrote this post but your sheer confidence in the probability pushed me to look at the problem differently and I was able to do it!

  Thank you!

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 30, 2018 at 3:00 pm

  #1988710

  QuietCoder - Monday, April 30, 2018 2:37 PM

  Lynn Pettis - Wednesday, April 25, 2018 12:55 PM

  QuietCoder - Wednesday, April 25, 2018 12:34 PM

  Lynn Pettis - Wednesday, April 25, 2018 11:56 AM

  QuietCoder - Wednesday, April 25, 2018 11:29 AM

  ScottPletcher - Wednesday, April 25, 2018 11:11 AM

  
  SELECT *
  FROM PeopleDetail PD
  WHERE PD.Name IN (
      SELECT Name
      FROM People
  )

  Scott,

  Thanks for looking into this. What I have posted is a very simplistic example of the problem I am trying to solve. The cursor and the SQL running within is much more complex and would not fit into the solution above.

  I am just trying to see if there is a way to do what I require

  Thanks

  Since you are the only one that can see the code, we have no idea if the cursor can be eliminated.  Just a guess, it probably can.  If you insist on keeping the cursor (or at least not seeing if it can be eliminated) just insert the results of each run of the code inside the cursor into a temporary table and then select the data from the table after you exit the cursor.

  Thanks Lynn! I think temp table is the way to go for me

  I am working on a presentation to eliminate cursors.  If you would like, PM me the code and we will see what happens.

  Lynn,

  Thank you for pushing me to eliminate the cursor. It did not seem possible when I wrote this post but your sheer confidence in the probability pushed me to look at the problem differently and I was able to do it!

  Thank you!

  Glad you took the time.  Feels good, doesn't it.

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  May 1, 2018 at 3:00 am

  #1988731

  QuietCoder - Wednesday, April 25, 2018 11:29 AM

  ScottPletcher - Wednesday, April 25, 2018 11:11 AM

  
  SELECT *
  FROM PeopleDetail PD
  WHERE PD.Name IN (
      SELECT Name
      FROM People
  )

  Scott,

  Thanks for looking into this. What I have posted is a very simplistic example of the problem I am trying to solve. The cursor and the SQL running within is much more complex and would not fit into the solution above.

  I am just trying to see if there is a way to do what I require

  Thanks

  Can you post the actual query instead of the simplified example please?
  😎

  I am yet to see a cursor query that is too complex to combine into a set based approach.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 1, 2018 at 8:42 am

  #1988788

  Eirikur Eiriksson - Tuesday, May 1, 2018 3:00 AM

  QuietCoder - Wednesday, April 25, 2018 11:29 AM

  ScottPletcher - Wednesday, April 25, 2018 11:11 AM

  
  SELECT *
  FROM PeopleDetail PD
  WHERE PD.Name IN (
      SELECT Name
      FROM People
  )

  Scott,

  Thanks for looking into this. What I have posted is a very simplistic example of the problem I am trying to solve. The cursor and the SQL running within is much more complex and would not fit into the solution above.

  I am just trying to see if there is a way to do what I require

  Thanks

  Can you post the actual query instead of the simplified example please?
  😎

  I am yet to see a cursor query that is too complex to combine into a set based approach.

  Eirikur, look up a couple of posts.