Transpose rows into column in PostgreSQL

Question

Here is my current PostgreSQL query:

 SELECT     
        c.ce_new_cat_short, 
        d.ver_desc_txt, 
        c.product_type, 
        round(sum(d.profit_amt/f.exch_rate)::numeric, 2) AS dp_dol, 
        sum(d.unit_qty) AS units
 FROM       
        ext_ce.gsam_v_data_detail as d
        LEFT JOIN
            (SELECT DISTINCT g.prmry_prfl_cd, g.wrkhrs_grp, g.brnd_fmly_desc_txt_adj FROM ext_ce.gsam_v_data_context AS g) AS vc
        ON vc.prmry_prfl_cd = d.prmry_prfl_cd,  
        ext_ce.gsam_data_context as c,
        ext_ce.info_fx_rates_v2 as f
 WHERE      
        c.product_type <> 'SALES AIDS'::text 
        AND c.cncpt_nr::numeric = d.prmry_prfl_cd
        AND f.offr_cmpgn_yr_nr = 2017
        AND d.mrkt_desc_txt = f.mrkt_desc_txt
 GROUP BY   
        c.ce_new_cat_short,  
        d.ver_desc_txt, 
        c.product_type;

I'm stuck trying to transpose column ver_desc_txt.

Here is my current output, alongside a desirable one.

How can I adjust this query to get the desirable output?

Possible duplicate of [Postgres - Transpose Rows to Columns](http://stackoverflow.com/questions/23060256/postgres-transpose-rows-to-columns) — Ricardo C, Mar 02 '17 at 21:21

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

0

You can use FILTER clause to limit aggregate functions:

SELECT     
        c.ce_new_cat_short, 
        c.product_type, 
        round(sum(CASE WHEN d.ver_desc_txt = 'FINAL LEADER LIST' THEN d.profit_amt/f.exch_rate ELSE 0 END)::numeric, 2) AS dp_dol_FLL, 
        round(sum(CASE WHEN d.ver_desc_txt = 'BEST OFFICAL PROJECTION' THEN d.profit_amt/f.exch_rate ELSE 0 END)::numeric, 2) AS dp_dol_BOP, 
        sum(CASE WHEN d.ver_desc_txt = 'FINAL LEADER LIST' THEN d.unit_qty ELSE 0 END) AS units_FLL,
        sum(CASE WHEN d.ver_desc_txt = 'BEST OFFICAL PROJECTION' THEN d.unit_qty ELSE 0 END) AS units_BOP
 FROM       
        ext_ce.gsam_v_data_detail as d
        JOIN ext_ce.gsam_data_context as c
            ON (c.cncpt_nr::numeric = d.prmry_prfl_cd)
        JOIN ext_ce.info_fx_rates_v2 as f
            ON ( d.mrkt_desc_txt = f.mrkt_desc_txt)
        LEFT JOIN (SELECT DISTINCT g.prmry_prfl_cd, g.wrkhrs_grp, g.brnd_fmly_desc_txt_adj FROM ext_ce.gsam_v_data_context AS g) AS vc
            ON vc.prmry_prfl_cd = d.prmry_prfl_cd
 WHERE      
        c.product_type <> 'SALES AIDS'::text 
        AND f.offr_cmpgn_yr_nr = 2017
 GROUP BY   
        1, 2

Also I would recommend you to use JOIN form of join - it allows to be assured that all part of join is joined correctly.

UPD:

For version 8.2.15 must use CASE WHERE instead FILTER

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 02 '17 at 20:16

Roman Tkachuk

3,096
1
16
15

Sorry, because there are no structures and sample tables i can not test table. I remove one ',' and add one. Please try now – Roman Tkachuk Mar 02 '17 at 21:28
shouldn't be: FILTER (where d.ver_desc_txt = 'FINAL LEADER LIST') AS dp_dol_FLL, ? – Bartek Mar 02 '17 at 21:43
Now query improved. After `dp_dol_FLL` ',' must be, after `ON vc.prmry_prfl_cd = d.prmry_prfl_cd` no. – Roman Tkachuk Mar 02 '17 at 21:46
hmm, still: ERROR: syntax error at or near "d" line 4 – Bartek Mar 02 '17 at 21:56
Sorry, one more mistake - WHERE in FILTER clause – Roman Tkachuk Mar 02 '17 at 21:59
no problems, appreciate your efforts. Now I got this: ERROR: filter clause specified, but round is not an aggregate function – Bartek Mar 02 '17 at 22:02
fixed (run round on while clause) – Roman Tkachuk Mar 02 '17 at 22:04
Roman, thanks but : ERROR: syntax error at or near "FILTER" line 4. – Bartek Mar 02 '17 at 22:11
Which version of postgresql do you use? – Roman Tkachuk Mar 02 '17 at 22:11
1.22.1 feb 2016. pg admin III – Bartek Mar 02 '17 at 22:14
SELECT version(); – Roman Tkachuk Mar 02 '17 at 22:15
1.22.1 - it is pgadmin – Roman Tkachuk Mar 02 '17 at 22:16
8.2.15 is this what U need? – Bartek Mar 02 '17 at 22:17
Wow. It is very old. Few minutes. – Roman Tkachuk Mar 02 '17 at 22:18
Please try. But I would recommend to upgrade postgresql. 8.2.15 was released 2009-12-14. There are many advantages in 9.x versions. – Roman Tkachuk Mar 02 '17 at 22:23

Transpose rows into column in PostgreSQL

1 Answers1

UPD: