I have a table like this :
AID AName GID GName PID PName A B
13 Ant 15 G15 13 904/M3 5 5
13 Ant 15 G15 16 905/M4 4 5
13 Ant 13 New 13 904/M3 6 2
13 Ant 13 New 16 905/M4 1 1
1 AUD 1 G1 13 904/M3 2 4
1 AUD 1 G1 16 905/M4 3 4
1 AUD 2 G2 13 904/M3 4 1
1 AUD 2 G2 16 905/M4 5 2
1 AUD 4 G4 13 904/M3 0 4
1 AUD 4 G4 16 905/M4 1 1
1 AUD 16 TGp 13 904/M3 2 5
1 AUD 16 TGp 16 905/M4 0 1
I am trying to write a store procedure which will help me to give a result like this :
AName P1A P1B P2A P2B Total
G15 5 5 4 5 19
New 6 2 1 1 10
Ant 11 7 5 6 29 (Total values of G15 and New)
G1 2 4 3 4 13
G2 4 1 5 2 12
G4 0 4 1 1 6
TGP 2 5 0 1 8
AUD 10 14 9 8 41 (Total values of G1, g2, G4 and TGP)
The values in column A, B represents P1A and P1B. Then P2A and P2B represents col. A and B values from other row with same AName. Ant and AUD gives the total only.
I am learning pivot and trying if i can figure out something, but can anyone help me out ?
I tried this SQL but it is not working, I am a newbie here
select AName, P1A, P1B, P2A, P2B
from
(
select AID,AName,GID,GName,PID,PName,A,B
) as sourceTable
Pivot
(
sum(A), sum(B) for AName in P1A, P1B, P2A, P2B
)
as PivotTable
