What SQL function do I need?

• andysfreeemail

  Mr or Mrs. 500

  Points: 563

  More actions

  August 4, 2009 at 2:54 am

  #137103

  I have a table

  Site_ID InvoiceNo InvoiceState

  1 1 1

  1 2 1

  1 3 5

  I want to produce a resultset showing how many invoices each site has in what state so in the above I'd like to get the following:

  SiteID State1Count State5Count State3Count

  1 2 1 0

  I think I can achieve this by writing a SQL statement for each count field I need and then selecting the total from each and joining to a record grouped by site id but I suspect there must be an inbuilt bit of syntax I'm missing which will let me do this. What I'd like (but which obviously wouldn't work is

  SELECT site_id, Count (State =1), Count(State=2), Count(State= 5), etc.

  Help.

  Thanks,

  Andrew

• Madhivanan-208264

  SSCertifiable

  Points: 7516

  More actions

  August 4, 2009 at 4:17 am

  #1034153

  You can make use of PIVOT function

  ---

  Madhivanan

  Failing to plan is Planning to fail