Skip to content
Advertisement

Pivot table without crosstab/tablefunc

I have a table like this

INPUT

id    author    size    file_ext
--------------------------------
1     a         13661   python
1     a         13513   cpp
1     a         1211    non-code
2     b         1019    python
2     b         6881    cpp
2     b         1525    python
2     b         1208    non-code
3     c         1039    python 
3     c         1299    cpp

I want to be able to pivot this table in the following manner

OUTPUT

id    author    size    python    cpp    non-code
-------------------------------------------------
1     a         13661   1         0      0
1     a         13513   0         1      0
1     a         1211    0         0      1 
2     b         1019    1         0      0
2     b         6881    0         1      0
2     b         1525    1         0      0
2     b         1208    0         0      1
3     c         1039    1         0      0
3     c         1299    0         1      0

All the articles that I can find online pivot tables based on a second column. My ultimate goal is to get one records per ID.

FINAL OUTPUT

id    author    size    python    cpp    non-code
-------------------------------------------------
1     a         28385   1         1      1
2     b         10633   2         1      1
3     c         2338    1         1      0

Here the values of the size, python, cpp, non-code columns are aggregated.

Advertisement

Answer

With conditional aggregation:

select 
  id, author,
  sum(size) size,
  sum((file_ext = 'python')::int) python,
  sum((file_ext = 'cpp')::int) cpp,
  sum((file_ext = 'non-code')::int) "non-code"
from tablename
group by id, author

See the demo.
Results:

> id | author |  size | python | cpp | non-code
> -: | :----- | ----: | -----: | --: | -------:
>  1 | a      | 28385 |      1 |   1 |        1
>  2 | b      | 10633 |      2 |   1 |        1
>  3 | c      |  2338 |      1 |   1 |        0
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement