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?

Answer 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 |