help with a sql statement

[Willie78]

Hi all

I have a problem.

i'm trying to create a view that will basically look like a pivot table but it can't be a pivot table because the system reading it will not understand it.

I have 1 table full of data

All i need to do is

SELECT SUM(tranbase)as fund_56
FROM table
where fund_code ='56'
group by pay_code

SELECT SUM(tranbase)as fund_07
FROM table
where fund_code ='07'
group by pay_code


about 50 times but I need them to go across in columns eg

fund_56 fund_07
12213 234234
2423 345534

etc

Can anyone help me

Cheers

Paul

 

[Krickles]

Try something like:

SELECT
   CASE WHEN fund_code = 56 THEN SUM(tranbase) fund_56 Else 0
   CASE WHEN fund_code = 07 THEN SUM(tranbase) fund_07 Else 0
.
.
.
FROM table

Let me know if this gets close and we can tweak from there.

Krickles | 1.6180

 

[Willie78]

Sorry Krickles

I can't see where you are going with this. There needs to ba a total by fund_code for each pay_code.

fund_56 fund_07
pay1 47 89
pay2 23 43

I hope this gives you more of an idea. If I could use a pivot table this would take mins.

Cheers

Paul