Creating a dynamic UNION query in a sp based on n records

• dan carr

  SSC Veteran

  Points: 221

  More actions

  August 13, 2004 at 3:57 pm

  #152259

  I have a table where 1 desk may have >1 portfolios.

  i.e. Desk 1 has portfolios W,X, Y, Z so the bolded Union query below works fine.

  But, Desk 2 contains portfolios A, B and C

  How can I dynamically create the UNION SQL statement (with TOP 10 from each portfolio)  i.e. A Grouped TOP 10 where the top 10 of each portfolio is UNIONed so if a DESK has 3 portfolios, 30 records will appear.  If it has 5 portfolios then 50 records will appear.

  Assume the portfolios are retrieved by:

  SELECT portfolios WHERE eskNum=@DeskNum">DeskNum=@DeskNum

  Assumes @DeskNum is passed as an IN param and the table looks like this:

  DeskNum     Portfolios

  1                W

  1                X

  1                 Y

  1                 Z

  2                 A

  2                 B

  2                 C

  ------------------------------------------------------------------

  SELECT TOP 10 *

  INTO #Report10

  FROM #FINAL10 WHERE Portfolio='W'

  UNION

  SELECT TOP 10 * FROM #FINAL10 WHERE Portfolio='X'

  UNION

  SELECT TOP 10 * FROM #FINAL10 WHERE Portfolio='Y'

  UNION

  SELECT TOP 10 * FROM #FINAL10 WHERE Portfolio='Z'

   

  Thank You,

  Dan

• Justin Doyle

  Valued Member

  Points: 63

  More actions

  August 16, 2004 at 7:04 am

  #519088

  What about something like this:

  declare @thisPortfolio char(1),

   @DeskNum int

  declare portfolio_cursor cursor

   for select distinct portfolio from portfolios where DeskNum = @DeskNum

  open portfolio_cursor

  fetch next from portfolio_cursor into @thisPortfolio

  while @@FETCH_STATUS = 0

  begin

   select top 10 *

    into #report10

    from #final10

    where portfolio = @thisPortfolio

   fetch next from portfolio_cursor into @thisPortfolio

  end

  close portfolio_cursor

  deallocate portfolio_cursor

  Cheers

  JD