Prev: How to pivot this
Next: concatenating
From: GW on 25 May 2010 03:16
Need help to pivot the following:-

counter type AMOUNT
------- ---- -------
1 A 10
1 A 10
2 A 20
3 A 30
3 A 30
4 A 40
5 A 50
5 A 50
1 B 10
2 B 20
3 B 30
3 B 30
3 B 30
4 B 40
5 B 50
5 B 50
5 B 50

Result :-

A B TOTAL
- - -
1 $$ $$ $$
2 $$ $$ $$
3 $$ $$ $$
4 $$ $$ $$
5 $$ $$ $$

Thank you.
From: John Bell on 25 May 2010 05:09
On Tue, 25 May 2010 00:16:01 -0700, GW <GW(a)discussions.microsoft.com>
wrote:

>Need help to pivot the following:-
>
>counter type AMOUNT
>------- ---- -------
>1 A 10
>1 A 10
>2 A 20
>3 A 30
>3 A 30
>4 A 40
>5 A 50
>5 A 50
>1 B 10
>2 B 20
>3 B 30
>3 B 30
>3 B 30
>4 B 40
>5 B 50
>5 B 50
>5 B 50
>
>Result :-
>
> A B TOTAL
> - - -
>1 $$ $$ $$
>2 $$ $$ $$
>3 $$ $$ $$
>4 $$ $$ $$
>5 $$ $$ $$
>
>Thank you.

Take a look at for some links
http://blogs.sqlxml.org/vinodkumar/archive/2006/05/30/6007.aspx

There are many example online of how you can pivot/cross tab in SQL
2000.

John
From: Plamen Ratchev on 25 May 2010 17:33
Take a look at CASE expressions:

SELECT counter,
SUM(CASE WHEN [type] = 'A' THEN amount ELSE 0 END) AS A,
SUM(CASE WHEN [type] = 'B' THEN amount ELSE 0 END) AS B,
SUM(amount) AS total
FROM Foo
GROUP BY counter;

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: How to pivot this
Next: concatenating