Mysql dynamically transform complex sql query table
I have a table with two columns, the following schema:
create table scan( `application_name` varchar(255) NOT NULL, `defect_type` varchar(255) NOT NULL);
And the data is filled in accordingly. The table stores data for "Application" and the corresponding "Type of defect". I want to do the following 2 actions on this table:
- Get the Top 3 "Defect Type" of a specific application as a percentage.
- Move the output from above where the values in "Defect Type" (defect_type) become columns and the corresponding percentage (finalPercent) as its value.
I can achieve 1 by following the SQL Fiddle:
However, I am unable to transfer the output as required. Ideally, there should be the following line, like after 1 and 2 together:
application_name | CrossSide | CSS | XML A | 33.33 | 33.33 | 16.67
Thank!
+3
source to share
1 answer
You can create a dynamic pivot query with group_concat
and then execute it:
set @sql = null;
set @total = (select count(*) as totalcount from scan where application_name = "a");
select group_concat(distinct
concat(
'round(((count(case when defect_type = ''',
defect_type,
''' then application_name end)/ @total ) * 100 ), 2) as `',
defect_type, '`'
)
) into @sql
from ( select defect_type
from scan
where application_name = "a"
group by defect_type
order by count(defect_type) desc
limit 3) t;
set @sql = concat(
'select application_name, ', @sql,
' from scan
where application_name = "a"
group by application_name'
);
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;
+2
source to share