Click here to Skip to main content
15,867,328 members
Please Sign up or sign in to vote.
1.00/5 (3 votes)
See more:
In SQL server ,I have following scenario

Actual:-

C#
select datename(month,Date)[Month],IsAttend[Attend]  from tblAttend


output:

Month   Attend
January    Yes
January    Yes
January    No
January    No
January    Yes
February   Yes
February   Yes
February   No
February   Yes
March      Yes
March      Yes
March      No
March      No
March      Yes
March      No
March      Yes

My required scenrio:

Month    Yes    No
January    3    2
Febuary   3   1
March     4    2

Please give a solution ASAP...
thanx in advanced...
Posted

Hi Singh...

Please try the below one.

SQL
select [monthname]  as mo,[attend] as att
from (select monthname ,attend  from mon)ps
pivot
(
count(attend)
for monthname in ([January],[February],[March])
)as pvt



It may be help u
 
Share this answer
 
Comments
Kailash_Singh 6-Jul-12 0:48am    
Hey Ece

Thanx for instance reply

but its not working for me.It's showing error after PIVOT

Incorrect syntax near (

if I remove parenthesis it's showing error after PIVOT

Incorrect syntax near count

I am using Sql server 2005 and I also checked its compatibility level, its 90
Hi Singh..

Sorry for the late reply,.


It will help u


SQL
select [monthname],[YES],[No]
from
(select * from mon )source pivot
(count(attend) for attend in ([Yes],[No]))pt

order by monthname
 
Share this answer
 
Comments
Arul R Ece 6-Jul-12 2:21am    
If it is correct means ,accept solution
Kailash_Singh 6-Jul-12 2:31am    
Sorry...........Ece........
But Same error...
:( :( :(
Arul R Ece 6-Jul-12 2:54am    
Hello ..i Got result like u want
Kailash_Singh 6-Jul-12 3:51am    
Hi Ece..
I tried again as following manner:

select [monthname],[YES],[No]
from
(select * from tblIssue )source pivot
(count(IsSolve) for IsSolve in ([Yes],[No]))pt
order by monthname

I got error:
Invalid column name 'monthname'

BTW..thnx a lot for your instance reply
Arul R Ece 6-Jul-12 2:55am    
select [monthname],[YES],[No]
from
(select * from mon )source pivot
(count(attend) for attend in ([Yes],[No]))pt

order by monthname

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900