Hi All,

I have a transact query which returns data in the following format....

Firm Product Total
-----------------------------------------------
FirmA P1 100
FirmA P2 200
FirmA P4 50
FirmB P1 150
FirmC P1 275
FirmC P5 90

(There can only be 5 different products).


The way I would like to see the data is like this....

Firm P1 P2 P3 P4 P5 Total
----------------------------------------------------------------------
FirmA 100 200 50 350
FirmB 150 150
FirmC 275 90 365

(Note, I'm not too worried about the total right now, just the column
placement)


My code currently looks like this... (and returns the first scenario)

SELECT
pg.Product,
f.Firm,
SUM(av.curr_mkt_amt) AS Total
FROM
acct_t a
JOIN fndg_t fg ON a.fndg_id = fg.fndg_id
JOIN prod_t p ON a.prod_id = p.prod_id
JOIN product_t pg ON p.prgp_cd = pg.prgp_cd
LEFT OUTER JOIN acct_dist_t ad ON a.acct_nbr = ad.acct_nbr
JOIN ofc_t o ON ad.ofc_id = o.ofc_id
JOIN firm_t f ON o.firm_id = f.firm_id
JOIN acct_value_hist_t av ON a.acct_nbr = av.acct_nbr
WHERE
fg.fndg_leg_id IN ('70','72')
GROUP BY
pg.prgp_nm,
f.firm_nm


Does anyone have any ideas if returning the second scenario (apart from the
totals) is feasiblie in SQL Server 2000 ?

Thanks,

Select Firm,
Sum(Case When Product = 'P1' Then av.curr_mkt_amt End) 'P1',
Sum(Case When Product = 'P2' Then av.curr_mkt_amt End) 'P2',
Sum(Case When Product = 'P3' Then av.curr_mkt_amt End) 'P3',
Sum(Case When Product = 'P4' Then av.curr_mkt_amt End) 'P4',
Sum(Case When Product = 'P5' Then av.curr_mkt_amt End) 'P5'
From acct_t A
Join fndg_t fg ON a.fndg_id = fg.fndg_id
Join prod_t p ON a.prod_id = p.prod_id
Join product_t pg ON p.prgp_cd = pg.prgp_cd
Left Join acct_dist_t ad ON a.acct_nbr = ad.acct_nbr
Join ofc_t o ON ad.ofc_id = o.ofc_id
Join firm_t f ON o.firm_id = f.firm_id
Join acct_value_hist_t av ON a.acct_nbr = av.acct_nbr
WHERE fg.fndg_leg_id IN ('70','72')
GROUP BY Firm


"quiglepops" wrote:

Woaa, that was quick, will give it a try, thanks a lot !!!


"CBretana" <cbretana (AT) areteIndNOSPAM (DOT) com> wrote

-- Left out the total column...

Select Firm,
Sum(Case When Product = 'P1' Then av.curr_mkt_amt End) P1,
Sum(Case When Product = 'P2' Then av.curr_mkt_amt End) P2,
Sum(Case When Product = 'P3' Then av.curr_mkt_amt End) P3,
Sum(Case When Product = 'P4' Then av.curr_mkt_amt End) P4,
Sum(Case When Product = 'P5' Then av.curr_mkt_amt End) P5,
Sum(av.curr_mkt_amt) Total
From acct_t A
Join fndg_t fg ON a.fndg_id = fg.fndg_id
Join prod_t p ON a.prod_id = p.prod_id
Join product_t pg ON p.prgp_cd = pg.prgp_cd
Left Join acct_dist_t ad ON a.acct_nbr = ad.acct_nbr
Join ofc_t o ON ad.ofc_id = o.ofc_id
Join firm_t f ON o.firm_id = f.firm_id
Join acct_value_hist_t av ON a.acct_nbr = av.acct_nbr
WHERE fg.fndg_leg_id IN ('70','72')
GROUP BY Firm


"CBretana" wrote:

Works Great ! Thanks for that.


"CBretana" <cbretana (AT) areteIndNOSPAM (DOT) com> wrote

I see you guys haven't discovered RAC

www.rac4sql.net