Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 how to group by colunm

Author  Topic 

sayer
Starting Member

35 Posts

Posted - 2013-02-16 : 07:26:05
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

52326 Posts

Posted - 2013-02-16 : 08:53:09
[code]
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]
[/code]

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

Go to Top of Page

sayer
Starting Member

35 Posts

Posted - 2013-02-16 : 09:33:07
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

52326 Posts

Posted - 2013-02-17 : 01:55:41
welcome

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

Go to Top of Page
   

- Advertisement -