convert column to row in postgresql database

Question

My current query is:

select rulename,status, count(*)
from triggered_rule_info
group by status, rulename

And the result is:

rulename    status  count
eorule1      ack    1
eorule1      open   1
eorule1      close  7
eorule2      open   1
eorule2      ack    1

But I want the result to be:

rulename     ack    open    close
eorule1       1      1       7
eorule2       1      1

How can I achieve this? My postgresql version is 9.4.

@Dipalis.: how can an answer for SQL Server that uses many SQL Server specific features be a duplicate for a Postgres question? — user330315
– user330315, Commented May 24, 2018 at 10:20

user330315 · Accepted Answer · 2018-05-24 10:20:01Z

1

For that you can use the filter clause:

select rulename
       count(*) filter (where status = 'ack') as ack,
       count(*) filter (where status = 'open') as open,
       count(*) filter (where status = 'close') as closed
from triggered_rule_info
group by rulename
order by rulename;

answered May 24, 2018 at 10:20

user330315

Sign up to request clarification or add additional context in comments.

Comments

Ravinder Reddy · Accepted Answer · 2018-05-24 10:17:14Z

0

You can make use of CASE - WHEN and GROUP BY clauses to get the desired result. You may require to add more CASE statements if status has more other values.

Example:

SELECT rulename
     , SUM( CASE status WHEN 'ack' THEN 1 ELSE 0 END ) AS "ack"
     , SUM( CASE status WHEN 'open' THEN 1 ELSE 0 END ) AS "open"
     , SUM( CASE status WHEN 'close' THEN 1 ELSE 0 END ) AS "close"
  FROM triggered_rule_info
 GROUP BY rulename
 ORDER BY rulename

answered May 24, 2018 at 10:17

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

Collectives™ on Stack Overflow

convert column to row in postgresql database

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related