SQL

• vinodhkumargv

  SSC Enthusiast

  Points: 147

  More actions

  February 12, 2014 at 6:36 am

  #396256

  Hi Friends,

  I have a table structure like

  ID status

  1 open

  1 close

  2 referral

  2 open

  2 close

  I want the result like

  ID open referral open

  1 1

  2 1

  The above table id 2 should give the referral open count based on referral in status. How can i write query for this?

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  February 12, 2014 at 6:48 am

  #1688328

  Not very clear what you really want. The best what you can do is to read the article from the link at the bottom of my signature, it should help you to post your question in a right way...

  May be you want this:

  declare @MyTable table (id int, [status] varchar(10))

  insert @MyTable values (1,'open'),(1,'close'),(2,'referral'),(2,'open'),(2,'close')

  select id

  ,sum(case [status] when 'open' then 1 else 0 end) as [open]

  ,sum(case [status] when 'referral' then 1 else 0 end) as [referral]

  ,sum(case [status] when 'close' then 1 else 0 end) as [close]

  from @MyTable

  group by id

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]

• SQLCJ

  SSCommitted

  Points: 1791

  More actions

  February 13, 2014 at 12:02 am

  #1688609

  Not sure which will perform better the one shared by Eugene Elutin or the shared below...

  It's been long since got a chance to use PIVOT thus the following solution

  SELECT IDD AS ID,[open],referral,[close] FROM

  (SELECT ID,STATUS,ID AS IDD FROM @MyTable) SOURCETAB

  PIVOT( COUNT(ID) FOR STATUS IN ([open],referral,[close])) AS PVT