Returning custome list of values with sequence no

• Charlottecb

  Hall of Fame

  Points: 3932

  More actions

  April 24, 2006 at 10:05 am

  #117398

  Hi All (SQL Server 2000)

  I am trying to return a list of departments under which are the appropriate employees and against each I need a calced row id.

  Above each is the type of department (only 2 types present Academic and Support - these are dummies)

  Staff table (StaffID, Surname, Forename, Dept ID)

  Dept Table (Dept ID, Description, Dept_Type [Academic / Support])

  Example of what I need

  Row ID, Record ID, Description

  1  1  ACADEMIC

  2  1  English

  3  56  Brown: John

  4  54  Jones: Jane

  5  1  Smith: Alex

  6  2  Math

  7  55  Bloggs: Sue

  8  12  Davis: Sarah

  9  2  SUPPORT

  10  5  Cleaning

  11  3  MacDonald: Bruce

  12  2  Smith: Alex

  13  4  Laundry

  14  22  Adams: Geena

  15  13  Black: Jan

  16  189  Brown: Ian

   

  Thanks for any help.

  CCB

• sushila

  SSC-Dedicated

  Points: 35293

  More actions

  April 24, 2006 at 10:34 am

  #633855

  If you're looking for numbering your rows "on the fly" you'll find several examples on this site...

  Here's one way of doing it...

  SELECT IDENTITY(INT,1,1) AS RowNum, col1, col2...
  INTO #tempResults
  FROM myTable
  WHERE...
  
  SELECT * FROM #tempResults
  
  DROP TABLE #tempResults
  

  You can also do it w/out using temp tables and using count(*) instead...just search this site...

  
  
  
  
  
  
  **ASCII stupid question, get a stupid ANSI !!!**

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  April 24, 2006 at 6:15 pm

  #633921

  I dunno... I don't think the count method could be made to work with any great effeciency here.  At best, you'd end up with a triangular join...

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)