Re: Hopefully a basic query question

On Sun, 13 Apr 2008 23:16:00 -0700, James Terrington wrote:

Good afternoon,

I am trying to create a query. Underlying data in table is:

Job No/Cost Type/$

J8000/1/3000
J8000/2/2000
J8000/2/5000
J8000/2/1000

In the above a Cost type of 1 equates to Labour cost, and a Cost type of 2
equates to Materials cost.

I want the resulting query to be as follows:

Job No/Labour$/Material$
J8000/3000/8000

However for the life of me I cannot figure out how to achieve this in Query
(assuming that I actually can...)

Is this possible, can someone help (after 5 hours of trying I am clueless)

Thanks

James

Hi James,

SELECT JobNo,
SUM(CASE WHEN CostType = 1 THEN Amount END) AS LabourCost,
SUM(CASE WHEN CostType = 2 THEN Amount END) AS MaterialCost
FROM YourTable
GROUP BY JobNo;

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
.