Is it possible to add select columns based on the field value?-CodePudding

I have a report in my web app to display the first 4 changes in value field in table. So basically, it will display the first 4 values in value column.

Currently, I created sql script and this will display the log level, auditable_id, value.

Current Query Result:

log_level	auditable_id	value
1	1	2
1	2	3
2	2	4
3	2	5
4	2	6

This should be the output:

auditable_id	log_level_1	log_level_2	log_level_3	log_level_4
1	2
2	3	4	5	6

Please see the attached screenshot.

CodePudding user response：

Check this:

select 
  auditable_id,
  max(case when log_level =1 then value end )  as log_level_1,
  max(case when log_level =2 then value end )  as log_level_2,
  max(case when log_level =3 then value end )  as log_level_3,
  max(case when log_level =4 then value end )  as log_level_4
  from tbl group by auditable_id;

Demo