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
