Displaying Data Across when data goes down! Has to be easy!

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!!
SheilaOn May 2, 8:04 pm, gwhi...@.kc.rr.com wrote:

Quote:

Originally Posted by

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

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