Postgresql how to change row to column

Question

lets say i have a table like this:

type       success    failed
type 1     10         1
type 2     4          0
type 3     5          3

and i want to create a table like this with query

type     state      count
type 1   success    10
type 1   failed     1
type 2   success    4
type 2   failed     0
type 3   success    5
type 3   failed     3

what query should i type to show a table like above?

using colpivot or crosstab?

Question stilla says "I want to crete a table" you might want to edit that — Mauricio Gracia Gutierrez
– Mauricio Gracia Gutierrez, Commented Aug 5, 2021 at 3:56
What have you tried ? is your current query showing you any errors ? — Mauricio Gracia Gutierrez
– Mauricio Gracia Gutierrez, Commented Aug 5, 2021 at 3:56

D-Shih · Accepted Answer · 2021-08-05 03:59:03Z

1

You can try to use UNION ALL

Query 1:

SELECT *
FROM (
  SELECT type,'success' state,success count  FROM T
  UNION ALL
  SELECT type,'failed' ,failed  FROM T
) t
ORDER BY type,state desc

Results:

|   type |   state | count |
|--------|---------|-------|
| type 1 | success |    10 |
| type 1 |  failed |     1 |
| type 2 | success |     4 |
| type 2 |  failed |     0 |
| type 3 | success |     5 |
| type 3 |  failed |     3 |

answered Aug 5, 2021 at 3:59

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

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

Comments

user330315 · Accepted Answer · 2021-08-05 05:52:56Z

1

Another option is to use a VALUES clause:

select t.type, u.*
from the_table t
  cross join lateral (  
    values ('success', t.success), ('failed', t.failed)
  ) as u(state,count)
order by t.type, u.state;

Online Example

answered Aug 5, 2021 at 5:52

user330315

Collectives™ on Stack Overflow

Postgresql how to change row to column

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