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!!
Sheila

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

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

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

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