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