return multiple rows as a single row

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

General SQL Server Forums

New to SQL Server Programming

return multiple rows as a single row

Author

Topic

ajsqlnoob
Starting Member

5 Posts

Posted - 2014-10-08 : 15:59:30

I’m a SQL noob and hoping someone can help me with this. I’m trying to return data in a single row.

Here’s what my table looks like:
Employee #,Hours Type,Total Hours
1234,Regular,40
1234,OT,8
1234,Regular,36
1234,OT,5
1234,Doubletime,5
1234,Doubletime,7
1234,OT,6
7777,Regular,8
7777,Regular,12
7777,Regular,25
7777,Overtime,8
7777,Doubletime,5

I need the results of my query to total each hours type and group together:
EmpNo,Sum Of Regular, Sum of Overtime,Sum of Doubletime
1234,76,19,12
7777,45,8,5

I don’t know how to get the data returned in a single row.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-08 : 16:33:56

[code]SELECT
[Employee #],
SUM( CASE WHEN [Hours Type] = 'Regular' THEN [Total Hours] ELSE 0 END) AS [Sum Of Regular],
SUM( CASE WHEN [Hours Type] = 'OT' THEN [Total Hours] ELSE 0 END) AS [Sum Of Overtime],
SUM( CASE WHEN [Hours Type] = 'Doubletime' THEN [Total Hours] ELSE 0 END) AS [Sum Of Doubletime]
FROM
YourTable
GROUP BY
[Employee #][/code]

ajsqlnoob
Starting Member

5 Posts

Posted - 2014-10-08 : 22:23:13

Thank you James!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources