SQL make rows as column

Question

i have log table which has a category column, like this

No	category	total	date
1	pembuatan	2	8/25/2021
2	pembuatan	4	8/26/2021
3	pengiriman	5	8/25/2021
4	pengiriman	1	8/26/2021
5	penerimaan	2	8/25/2021
6	penerimaan	4	8/26/2021
7	penghapusan	5	8/25/2021
8	penghapusan	3	8/26/2021
9	pengubahan	2	8/25/2021
10	pengubahan	1	8/26/2021

is there a way to make the row category into a column and add up the values in the total column? the result will be like this

date	pembuatan	pengiriman	penerimaan	penghapusan	pengubahan
8/25/2021	2	5	2	5	2
8/26/2021	4	1	4	3	1

and actually, the table above is not the original table, the original table has a structure like this

id, berkas_nomor, user1_nip, user2_nip, keterangan, category, date

and I use the sql code below so that the results look like the first table

SELECT c.category, 
        COUNT(l.id) jumlah,  
        d.date as tanggal
 FROM (SELECT DISTINCT category FROM log) c
 CROSS JOIN (SELECT DISTINCT DATE(date) date FROM log WHERE DATE(date) BETWEEN '2021-01-01' AND '2021-12-12') d
 LEFT JOIN log l ON l.category = c.category AND DATE(l.date) = d.date
 GROUP BY c.category, d.date
 ORDER BY c.category ASC

So, back to my question, is it possible with the log table structure that I have above, I can change the types of categories in the category column to be a column instead of a row? Thanks

Do you have exactly those 5 categories in your data or do you want a solution that changes the number of columns dynamically. — Salman A, Sep 06 '21 at 08:47

score 1 · Answer 1 · answered Sep 06 '21 at 08:47

If you do know the list of columns you want to show, you can do it with standard sql using case when and aggregation.

select
  date,
  max(case when category = 'pembuatan' then total else null end) as pembuatan,
  max(case when category = 'pengiriman' then total else null end) as pengiriman,
  max(case when category = 'penerimaan' then total else null end) as penerimaan,
  max(case when category = 'penghapusan' then total else null end) as penghapusan,
  max(case when category = 'pengubahan' then total else null end) as pengubahan
from log_table
group by date

SQL make rows as column

1 Answers1