SQL

Question

Post reply

SQL

vinodhkumargv

SSC Enthusiast

Points: 117
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?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

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: 1781 More actions · Answer 2

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