How to group multiple rows in to a single row

Question

I've used crosstab function and ended up with a table that has a single column value on each row.

|  ID  |  c1  |  c2  |  c3  |
-----------------------------
|  1   |      |  1   |      |
|  1   |  4   |      |      |
|  1   |      |      |  12  |
|  15  |  2   |      |      |
|  15  |      |      |  3   |

and want to change it so that the ID is distinct and all values are on the same row:

|  ID  |  c1  |  c2  |  c3  |
-----------------------------
|  1   |  4   |  1   |  12  |
|  15  |  2   |      |  3   |

What is the best way to do this? I could use an aggregate function like SUM and GROUP BY id but that makes pointless calculations that aren't required.

You forgot to provide the statement you used. Plus necessary details to reproduce. — Erwin Brandstetter, Jun 12 '23 at 02:22
@JimJones: We wouldn't need `crosstab()` for the simple aggregation. — Erwin Brandstetter, Jun 12 '23 at 13:17

Erwin Brandstetter · Answer 1 · 2023-06-12T13:40:17.550

Basically, with crosstab() from the additional module tablefunc:

SELECT *
FROM   crosstab(
   'SELECT id, key, value
    FROM   tbl
    ORDER  BY 1' -- !

  , $$VALUES ('c1'), ('c2'), ('c3')$$
   ) AS ct ("ID" text, c1 int, c2 int, c3 int);

See (with detailed explanation):

Pivot on Multiple Columns using Tablefunc

Or with pure SQL:


SELECT id
     , min(value) FILTER (WHERE key = 'c1') AS c1
     , min(value) FILTER (WHERE key = 'c2') AS c2
     , min(value) FILTER (WHERE key = 'c3') AS c3
FROM   tbl
ORDER  BY 1;

Optionally add WHERE key = ANY('{c1,c2,c3}') to each query.
Adapt to your actual, undisclosed data types and requirements.

Each has its pros and cons. See:

Or provide more details in the question. – Erwin Brandstetter Jun 12 '23 at 13:10 — Erwin Brandstetter, Jun 12 '23 at 13:10

How to group multiple rows in to a single row

1 Answers1