MS Query Case Statements - SQL Server Forums

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

Database Design and Application Architecture

MS Query Case Statements

Author

Topic

Rowan Evans
Starting Member

2 Posts

Posted - 2009-04-01 : 20:56:31

I have an existing data base that has the following information

Job Number___Trade__________Hours
23424______Electrician_______10
23424________Rigger___________5
23424________Fitter___________8

I am trying to write a query that will put it into the following form

Job Number___Electrician___Rigger___Fitter
23424___________10_________5________8

PLease help!

Thanks Rowan

zzzbla
Starting Member

13 Posts

Posted - 2009-04-03 : 01:32:31

Hi Rowan,

Are the columns in the desired resultset fixed? Which version SQL Server are you using?

If the columns are fixed and you use SQL Server 2005, you could use PIVOT to get the resultset you want:

select *
from (
select jobnumber, trade, hours
from tblName) t
PIVOT ( SUM(hours)
FOR trade IN ([electrician],[rigger],[fitter])
) AS pvt

If you're on 2000, you'll have to use the more complex looking syntax:

SELECT jobnumber,
sum(CASE WHEN trade='electrician' then hours else 0 end) AS [electrician],
sum(CASE WHEN trade='rigger' then hours else 0 end) AS [rigger],
sum(CASE WHEN trade='fitter' then hours else 0 end) AS [fitter]
from tableName
group by jobnumber

If you don't know the columns (that is, all the trades) in advance, then you'll have to write a query that dynamically generates a query like the ones above, or use pivoting/cross tab features in the client application (available in Excel or Reporting Services for example).

Cheers,
S. Neumann

Rowan Evans
Starting Member

2 Posts

Posted - 2009-04-03 : 02:05:26

Thanks for the reply.

I am not sure what version of SQL server i am using. Probably a stupid question but how do you find out? I am not even sure that i posted in the right area. I am building my query in Microsoft Query.

Regards,
Rowan

zzzbla
Starting Member

13 Posts

Posted - 2009-04-03 : 06:41:16

Microsoft Query in excel?

As I wrote in my previous post you could use Excel Crosstab in order to accomplish the same thing.

You can check SQL version with the query:
SELECT @@VERSION

You could also simply execute the queries from my previous post (don't forget to change "tableName" to your table name) - the first one will execute only on SQL 2005 and above.

Hope that helps,
S. Neumann

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources