SQL query : transform rows to columns

Question

Here's an example of my table.

I need to do a query that shows those IDs who have 0 as a fee on one of two months (11 or 12) or both.

So from the example, I need to show ID 1,3,4 but not 2, like on the screenshot below.

I tried the query below:

SELECT 
    t1.id, t1.month, t1.fee, t2.id, t2.month, t2.fee
FROM
    table t1, table t2
WHERE t1.id = t2.id
  AND t1.month = '11'
  AND t2.month = '12'
  AND (t1.fee = 0 OR t2.fee = 0);

But with this query, I only see ID 1,3 but not ID 4. I guess it's because of t1.id = t2.id but no idea how to do otherwise.

https://stackoverflow.com/questions/20618323/create-a-pivot-table-with-postgresql might help. — jarlh, Jun 20 '19 at 10:23
Tip of today: Always use modern, explicit `JOIN` syntax. Easier to write (without errors), easier to read and maintain, and easier to convert to outer join if needed! — jarlh, Jun 20 '19 at 10:23

Gordon Linoff · Answer 1 · 2019-06-20T14:29:25.013

3

You can use conditional aggregation. In Postgres, this can make use of the filter syntax:

SELECT t.id,
       11 as month,
       MAX(t.fee) FILTER (WHERE t.month = 11) as fee_11, 
       12 as month,
       MAX(t.fee) FILTER (WHERE t.month = 12) as fee_12
FROM t
GROUP BY t.id
HAVING MAX(t.fee) FILTER (WHERE t.month = 11) = 0 OR
       MAX(t.fee) FILTER (WHERE t.month = 12) = 0;

Note: The two month columns are redundant.

edited Jun 20 '19 at 14:29

answered Jun 20 '19 at 10:34

Gordon Linoff

1,242,037
58
646
786

hi, I just tried your query and it doesn't understand fee_11 / fee_12 of the last line. do you have an idea why ? – Jun 20 '19 at 13:28
@ayay . . . I just forget that Postgres doesn't support column aliases in the `HAVING` clause. – Gordon Linoff Jun 20 '19 at 14:29

score 2 · Answer 2 · answered Jun 20 '19 at 10:33

2

you need conditional aggregation

  select id,month,max(case when month=11 then fee end) fee11,
     max(case when month=12 then fee end) as fee12
    from (
    select * from table t1
    where t1.id in ( select id from table where fee=0)
    ) a group by id,month

answered Jun 20 '19 at 10:33

Zaynul Abadin Tuhin

31,407
5
33
63

Hi, your query worked out but it doesn't give what exactly I want. for example, ID 3 and 1 are appeared 2 times... – Jun 20 '19 at 13:29

score 0 · Accepted Answer · answered Jun 20 '19 at 11:06

0

Sql ansi compliant query

    SELECT id, 
        MAX(CASE WHEN MONTH = 11 THEN MONTH ELSE NULL END) AS month11, 
        MAX(CASE WHEN MONTH = 11 THEN fee ELSE NULL END) AS fee11, 
        MAX(CASE WHEN MONTH = 12 THEN MONTH ELSE NULL END) AS month12, 
        MAX(CASE WHEN MONTH = 12 THEN fee ELSE NULL END ) AS fee12
    FROM t
    GROUP BY id
    HAVING ( MAX(CASE WHEN MONTH = 11 THEN fee ELSE NULL END) = 0 OR MAX(CASE WHEN MONTH = 12 THEN fee ELSE NULL END ) = 0 )
    ORDER BY id

answered Jun 20 '19 at 11:06

nicolasL

136
1
7

however, I wonder if there's a way to do it using "outer join" instead of "max(case...)", do you have an idea ? – Jun 20 '19 at 13:31
1

It would be a full outer join then, you may have an id with the month 12 without any row with the month 11 for the corresponding id. Also, it would mean scanning the table t twice. `sql SELECT COALESCE( t11.id, t12.id ) AS id, t11.month AS month11, t11.fee AS fee11, t12.month AS month12, t12.fee AS fee12 FROM (SELECT * FROM t WHERE MONTH = 11 ) AS t11 FULL OUTER JOIN ( SELECT * FROM t WHERE MONTH = 12 ) AS t12 ON t11.id = t12.id WHERE ( t11.fee = 0 OR t12.fee = 0 ) ` – nicolasL Jul 02 '19 at 13:57

SQL query : transform rows to columns

3 Answers3