I have a table with:

Name, Qtr, Amount
Tom, 1, 100
Bob, 1, 123
Tom, 2, 234
Bob, 2, 456
Steve, 1, 565
Steve, 2, 898

I want the query to return:

Name, Qtr 1 Amount, Qtr 2 Amount
Bob 123 456
Steve 565 898
Tom 100 234

I can't seem to figure this out! Any help would be appreciated!!
Sheila

On May 2, 8:04 pm, gwhi... (AT) kc (DOT) rr.com wrote:
Search this newsgroup for the word "crosstab."

Here is one way to do this:

SELECT Name,
SUM(CASE WHEN Qtr = 1
THEN Amount
ELSE 0 END) AS 'Qtr 1 Amount',
SUM(CASE WHEN Qtr = 2
THEN Amount
ELSE 0 END) AS 'Qtr 2 Amount'
FROM Foo
GROUP BY Name;


In SQL Server 2005 it can be done with the PIVOT operator:

SELECT Name,
[1] AS 'Qtr 1 Amount',
[2] AS 'Qtr 2 Amount'
FROM Foo
PIVOT
(SUM(Amount) FOR Qtr IN ([1], [2])) AS P;


HTH,

Plamen Ratchev
http://www.SQLStudio.com