mysql row column conversion sample code

mysql row column conversion sample code

1. Demand

We have three tables. We need to classify and count the different antibiotic sensitivity results over a period of time, that is, the drugs_result of the report_item_drugs table, the proportion under different projects project_name and different antibiotics antibiotic_dict_name, and display the drug sensitivity results on the rows . The effect is as follows:

The three original tables (only the required fields are taken as examples) are:

Report Form

Project List

Antibiotics table (drug sensitivity results drugs_result is a column of values)

2. Implementation

1. Calculate the total number of detected cases by project and antibiotic group

SELECT 
 A.project_name,A.antibiotic_dict_name,SUM(nums) AS total number of detections FROM 
(
      SELECT i.project_name,d.antibiotic_dict_name,d.drugs_result,COUNT(d.id) AS nums FROM `report` r
       RIGHT JOIN report_item i ON r.id=i.report_id
       RIGHT JOIN report_item_drugs d ON d.report_item_id=i.id
       WHERE r.report_status=2 AND r.add_date BETWEEN '2020-01-01' AND '2020-12-30' 
       GROUP BY i.project_id,d.antibiotic_dict_id,d.drugs_result
 ) A
 GROUP BY A.project_name,A.antibiotic_dict_name

2. Calculate the number of different drug sensitivity results according to the project, antibiotic, and drug sensitivity results

SELECT i.project_name,d.antibiotic_dict_name,IF(d.drugs_result<>'', d.drugs_result, 'Not filled in') AS drugs_result,COUNT(d.id) AS quantity FROM `report` r
RIGHT JOIN report_item i ON r.id=i.report_id
RIGHT JOIN report_item_drugs d ON d.report_item_id=i.id
WHERE r.report_status=2 AND r.add_date BETWEEN '2020-01-01' AND '2020-12-30' 
GROUP BY i.project_id,d.antibiotic_dict_id,d.drugs_result

3. Link the two results together

SELECT 
      BB.project_name,BB.antibiotic_dict_name,BB.drugs_result,BB.`quantity`,AA.`total number of detections`
    FROM 
        (
              SELECT 
                A.project_name,A.antibiotic_dict_name,SUM(nums) AS total number of detections FROM 
              (
                    SELECT i.project_name,d.antibiotic_dict_name,d.drugs_result,COUNT(d.id) AS nums FROM `report` r
                    RIGHT JOIN report_item i ON r.id=i.report_id
                    RIGHT JOIN report_item_drugs d ON d.report_item_id=i.id
                    WHERE r.report_status=2 AND r.add_date BETWEEN '2020-01-01' AND '2020-12-30' 
                    GROUP BY i.project_id,d.antibiotic_dict_id,d.drugs_result
              ) A
              GROUP BY A.project_name,A.antibiotic_dict_name
        ) AA 
        RIGHT JOIN 
        (
              SELECT i.project_name,d.antibiotic_dict_name,IF(d.drugs_result<>'', d.drugs_result, 'Not filled in') AS drugs_result,COUNT(d.id) AS quantity FROM `report` r
              RIGHT JOIN report_item i ON r.id=i.report_id
              RIGHT JOIN report_item_drugs d ON d.report_item_id=i.id
              WHERE r.report_status=2 AND r.add_date BETWEEN '2020-01-01' AND '2020-12-30' 
              GROUP BY i.project_id,d.antibiotic_dict_id,d.drugs_result            
        )BB ON AA.project_name=BB.project_name AND AA.antibiotic_dict_name=BB.antibiotic_dict_name
    WHERE AA.`Total number of detections`<>'

4. Generally speaking, the number of different drug sensitivity and the total number are available in the previous step, so the ratio can be directly calculated.

However, what we need is to display the drug sensitivity on the row, and directly calculating the ratio does not meet the requirements, so we need to convert the column into a row

We use case when to convert rows and columns, and convert the drug sensitivity results into easy-to-read Chinese characters according to the dictionary 


SELECT
  C.project_name project name, C.antibiotic_dict_name antibiotic name, C.`total number of detections`,
  SUM(CASE C.`drugs_result` WHEN 'D' THEN C.`Quantity` ELSE 0 END ) AS 'Dose-dependent sensitivity',
  CONCAT(SUM(CASE C.`drugs_result` WHEN 'D' THEN FORMAT(C.`Quantity`/C.`Total number of detections`*100,2) ELSE 0 END),'%') AS 'Dose-dependent sensitivity ratio',
  SUM(CASE C.`drugs_result` WHEN 'R' THEN C.`Quantity` ELSE 0 END ) AS 'Drug resistance',
  CONCAT(SUM(CASE C.`drugs_result` WHEN 'R' THEN FORMAT(C.`Quantity`/C.`Total number of detections`*100,2) ELSE 0 END),'%') AS 'Drug resistance ratio',
  SUM(CASE C.`drugs_result` WHEN 'S' THEN C.`Quantity` ELSE 0 END ) AS 'Sensitive',
  CONCAT(SUM(CASE C.`drugs_result` WHEN 'S' THEN FORMAT(C.`Quantity`/C.`Total number of detections`*100,2) ELSE 0 END),'%') AS 'Sensitive ratio',
  SUM(CASE C.`drugs_result` WHEN 'I' THEN C.`Quantity` ELSE 0 END ) AS 'Agency',
  CONCAT(SUM(CASE C.`drugs_result` WHEN 'I' THEN FORMAT(C.`Quantity`/C.`Total Detection`*100,2) ELSE 0 END),'%') AS 'Intermediary Ratio',
  SUM(CASE C.`drugs_result` WHEN 'n1' THEN C.`quantity` ELSE 0 END ) AS 'non-sensitive',
  CONCAT(SUM(CASE C.`drugs_result` WHEN 'n1' THEN FORMAT(C.`Quantity`/C.`Total number of detections`*100,2) ELSE 0 END),'%') AS 'Non-sensitive ratio',
  SUM(CASE C.`drugs_result` WHEN 'N' THEN C.`Quantity` ELSE 0 END ) AS 'None',
  CONCAT(SUM(CASE C.`drugs_result` WHEN 'N' THEN FORMAT(C.`Quantity`/C.`Total Detection`*100,2) ELSE 0 END),'%') AS 'No ratio',
  SUM(CASE C.`drugs_result` WHEN 'Not filled in' THEN C.`Quantity` ELSE 0 END ) AS 'Not filled in',
  CONCAT(SUM(CASE C.`drugs_result` WHEN 'Not filled in' THEN FORMAT(C.`Quantity`/C.`Total number of detections`*100,2) ELSE 0 END),'%') AS 'Not filled in ratio'
FROM
(
    SELECT 
      BB.project_name,BB.antibiotic_dict_name,BB.drugs_result,BB.`quantity`,AA.`total number of detections`
    FROM 
        (
              SELECT 
                A.project_name,A.antibiotic_dict_name,SUM(nums) AS total number of detections FROM 
              (
                    SELECT i.project_name,d.antibiotic_dict_name,d.drugs_result,COUNT(d.id) AS nums FROM `report` r
                    RIGHT JOIN report_item i ON r.id=i.report_id
                    RIGHT JOIN report_item_drugs d ON d.report_item_id=i.id
                    WHERE r.report_status=2 AND r.add_date BETWEEN '2020-01-01' AND '2020-12-30' 
                    GROUP BY i.project_id,d.antibiotic_dict_id,d.drugs_result
              ) A
              GROUP BY A.project_name,A.antibiotic_dict_name
        ) AA 
        RIGHT JOIN 
        (
              SELECT i.project_name,d.antibiotic_dict_name,IF(d.drugs_result<>'', d.drugs_result, 'Not filled in') AS drugs_result,COUNT(d.id) AS quantity FROM `report` r
              RIGHT JOIN report_item i ON r.id=i.report_id
              RIGHT JOIN report_item_drugs d ON d.report_item_id=i.id
              WHERE r.report_status=2 AND r.add_date BETWEEN '2020-01-01' AND '2020-12-30' 
              GROUP BY i.project_id,d.antibiotic_dict_id,d.drugs_result            
        )BB ON AA.project_name=BB.project_name AND AA.antibiotic_dict_name=BB.antibiotic_dict_name
    WHERE AA.`Total number of detections`<>'                                        
) C
GROUP BY C.project_name,C.antibiotic_dict_name;

5. Check the results and convert successfully


This is the end of this article about the sample code of MySQL row-column conversion. For more relevant MySQL row-column conversion content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • Database implementation of row and column conversion (mysql example)
  • Implementation of dynamic conversion of mysql rows and columns (contingency table, cross table)

<<:  Detailed explanation of js's event loop event queue in the browser

>>:  When is it appropriate to use dl, dt, and dd?

Recommend

Detailed explanation of the knowledge points of using TEXT/BLOB types in MySQL

1. The difference between TEXT and BLOB The only ...

Detailed explanation of the use of Vue3 state management

Table of contents background Provide / Inject Ext...

Gogs+Jenkins+Docker automated deployment of .NetCore steps

Table of contents Environmental Description Docke...

Detailed installation and configuration tutorial of MySQL flashback tool binlog2sql

Overview binlog2sql is an open source MySQL Binlo...

Media query combined with rem layout in CSS3 to adapt to mobile screens

CSS3 syntax: (1rem = 100px for a 750px design) @m...

MySQL Series 10 MySQL Transaction Isolation to Implement Concurrency Control

Table of contents 1. Concurrent access control 2....

How to implement the builder pattern in Javascript

Overview The builder pattern is a relatively simp...

About nginx to implement jira reverse proxy

Summary: Configure nginx reverse proxy jira and i...

Sequence implementation method based on MySQL

The team replaced the new frame. All new business...

Several ways to solve the problem of floating causing the height of the parent element to collapse in CSS

In the past, float was often used for layout, but...

CSS to achieve dynamic secondary menu

Dynamically implement a simple secondary menu Whe...

CentOS 6 uses Docker to deploy Zookeeper operation example

This article describes how to use docker to deplo...

Detailed explanation of the solution to the failure of VMware to open the module diskearly

VMWare (Virtual Machine ware) is a "virtual ...

Some wonderful uses of URL objects in JavaScript

Table of contents Preface Parsing parameters Modi...

NodeJs high memory usage troubleshooting actual combat record

Preface This is an investigation caused by the ex...