How to do 1 Result Set with n rows instead of n RS with 1 row?

• calgaryChinese

  Grasshopper

  Points: 12

  More actions

  May 3, 2006 at 6:33 pm

  #76520

  Hi there

  I know the title sounds stupid but I don't really know the best way to word it.

  I have to do a count of 2 employees in several tables. So I can do for each table that I want to get a count, such as

  SELECT 'tblA' AS tblName,

  (SELECT Count(*) FROM tblA WHERE EmployeeId = 'EMP1') AS CounterEMP1,

  (SELECT Count(*) FROM tblA WHERE EmployeeId = 'EMP2') AS CounterEMP2

  SELECT 'tblB' AS tblName,

  (SELECT Count(*) FROM tblA WHERE EmployeeId = 'EMP1') AS CounterEMP1,

  (SELECT Count(*) FROM tblA WHERE EmployeeId = 'EMP2') AS CounterEMP2

  But say if I have 15 tables to look at, I'll end up having 15 result sets.

  Is there a way to rewrite it so, instead of having 15 result sets, each with 1 row, I can have 1 result set of 15 rows?

  I suppose you can do a cursor and/or stored procedure, but I'd like to know if there's a way to do just one SELECT statement to accomplish the above.

  Thanks

  CalgaryChinese

• John Rowan

  SSC Guru

  Points: 56440

  More actions

  May 3, 2006 at 9:59 pm

  #635829

  You can combine your select statements into one result set using UNION.  Look it up in BOL.  You may also consider using a group by to get your totals.  For example:

  SELECT 'TblA' as TblName,

   EmployeeID as 'Employee',

   COUNT(*) as 'Count'

  FROM TblA

  WHERE EmployeeID in ('EMP1','EMP2')

  GROUP BY EmployeeID

  UNION ALL

  SELECT 'TblB' as TblName,

   EmployeeID as 'Employee',

   COUNT(*) as 'Count'

  FROM TblB

  WHERE EmployeeID in ('EMP1','EMP2')

  GROUP BY EmployeeID

  ORDER BY TblName, EmployeeID

   

  John Rowan

  ======================================================
  ======================================================
  Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

• calgaryChinese

  Grasshopper

  Points: 12

  More actions

  May 4, 2006 at 9:14 am

  #635981

  Hi John

  Thanks a lot!  That's really what I'm looking for.

  One final catch though: if the table does not contain employees EMP1 or EMP2, there won't be any rows returned.

  Can I force it, do you know?

  Thanks

  CC