SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 how to group by colunm
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sayer
Starting Member

Saudi Arabia
35 Posts

Posted - 02/16/2013 :  07:26:05  Show Profile  Reply with Quote
i have tow tables task and month
i want create report for task
task table month
-----id--------month_id------state |----id-------name
11 1 good | 1 January
12 1 very good | 2 February
13 2 good | 3 March
14 3 bad
15 1 good
16 1 good
17 1 good
-----------------------------------------------
report
---month--------good--------very good----------bad
January 5 1 0
February 1 0 0
March 0 0 1

how to create like this report
i used sqlce 3.5
and VS 2010 C#

http://aman-services.net
for office
???? ???? ???????

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/16/2013 :  08:53:09  Show Profile  Reply with Quote

SELECT m.[name],
SUM(CASE WHEN state = 'good' THEN 1 ELSE 0 END) AS [good],
SUM(CASE WHEN state = 'very good' THEN 1 ELSE 0 END) AS [very good],
SUM(CASE WHEN state = 'bad' THEN 1 ELSE 0 END) AS [bad]
FROM [month] m
INNER JOIN [task table] t
ON t.month_id = m.id
GROUP BY [name]


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

sayer
Starting Member

Saudi Arabia
35 Posts

Posted - 02/16/2013 :  09:33:07  Show Profile  Reply with Quote
quote:
Originally posted by visakh16


SELECT m.[name],
SUM(CASE WHEN state = 'good' THEN 1 ELSE 0 END) AS [good],
SUM(CASE WHEN state = 'very good' THEN 1 ELSE 0 END) AS [very good],
SUM(CASE WHEN state = 'bad' THEN 1 ELSE 0 END) AS [bad]
FROM [month] m
INNER JOIN [task table] t
ON t.month_id = m.id
GROUP BY [name]

thanks for your solution
it is help me
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





http://aman-services.net
for office
???? ???? ???????
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/17/2013 :  01:55:41  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000