Prev: Checking if a view is being used
Next: How to pivot this in SQL 2K
From: GW on 25 May 2010 02:45
Need help...

how 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: rgn on 25 May 2010 16:30
Would this help:

Create table x(kounter int,TypeA char(1), Amount int)
GO
INSERT x(kounter,TypeA,Amount) VALUES (1,'A',10)
INSERT x(kounter,TypeA,Amount) VALUES (2,'A',20)
INSERT x(kounter,TypeA,Amount) VALUES (3,'A',40)
INSERT x(kounter,TypeA,Amount) VALUES (3,'A',50)
INSERT x(kounter,TypeA,Amount) VALUES (1,'B',10)
INSERT x(kounter,TypeA,Amount) VALUES (1,'B',30)
INSERT x(kounter,TypeA,Amount) VALUES (2,'B',40)
INSERT x(kounter,TypeA,Amount) VALUES (3,'B',50)
INSERT x(kounter,TypeA,Amount) VALUES (1,'C',10)
INSERT x(kounter,TypeA,Amount) VALUES (1,'C',50)
INSERT x(kounter,TypeA,Amount) VALUES (2,'C',30)
INSERT x(kounter,TypeA,Amount) VALUES (3,'C',40)

WITH CTETable (kounter,A,B,C) AS(
SELECT kounter,[A],[B],[C] --, SUM([A]+[B])
FROM (
Select kounter, TypeA,Amount FROM X ) AS S
PIVOT (
SUM(Amount)
FOR TypeA IN ([A],[B],[C])) AS PivotTable)
SELECT kounter,A,B,C, A+B+C Total FROm CTETable



"GW" wrote:

> Need help...
>
> how 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.
 | 
Pages: 1
Prev: Checking if a view is being used
Next: How to pivot this in SQL 2K