Getting row numbers in a query

• David Young

  Valued Member

  Points: 65

  More actions

  September 27, 2004 at 1:05 pm

  #60526

  Is there a way to get row numbers from a select query? Here is what I am trying to do.

  I have a table with two fields and a composite index. It looks like this.

  UserId, BrokerId

  7, 442

  7, 498

  7, 495

  I need the data to appear like this.

  UserId, Broker1, Broker2, Broker3

  7, 442, 498, 495

  I know how to achieve the desired results with a CASE statement if I can get to the row numbers. eg.

  SELECT UserId, Broker1 = CASE WHEN Rownumber = 1 THEN BrokerId END

  Is this possible, or does someone else know of a different way to achieve the desired result?

  Thanks

• Aaron Templeton

  SSCarpal Tunnel

  Points: 4655

  More actions

  September 27, 2004 at 1:54 pm

  #524569

  Try out the following:

  http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=132980

  http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=133911

   

  The opposite direction:

  http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=133646