[SOLVED] Pivot MySQL Table Using date as Column Name

Issue

How can I get this table
enter image description here

to look like this something like this.

Here’s the table definition and Sample Data.

CREATE TABLE result (
universal_id varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
flag varchar(1) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
measurement varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
units varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
reference_range varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
value_conv varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
unit_conv varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
ref_conv varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
test_code varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
id int(11) NOT NULL AUTO_INCREMENT,
order_no int(11) NULL DEFAULT NULL,
sample_id varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
test_group varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
date datetime(0) NULL DEFAULT NULL,
patient_id varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
status varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
instrument varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
his_code varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
his_mainid varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
section varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
sub_section varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
print_status int(1) NULL DEFAULT NULL,
PRIMARY KEY (id) USING BTREE,
INDEX SampleID(sample_id) USING BTREE,
INDEX Section(section) USING BTREE,
INDEX SubSection(sub_section) USING BTREE,
INDEX TestCode(test_code) USING BTREE,
INDEX Machine(instrument) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 463617 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = DYNAMIC;

INSERT INTO `result` VALUES ('White Blood Cells', '', '7.99', 'x10^9/L', '4.00-10.00', '8.0', 'x10^9/L', '4.0-10.0', 'WBC', 107649, 4, '0006261', 'Complete Blood Count', '2021-04-14 05:22:47', '271489', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC', 1);
INSERT INTO `result` VALUES ('Red Blood Cells', '', '5.24', 'x10^12/L', '3.50-5.50', '5.24', 'x10^12/L', '3.50-5.50', 'RBC', 107650, 3, '0006261', 'Complete Blood Count', '2021-04-14 05:22:47', '271489', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC', 1);
INSERT INTO `result` VALUES ('Hemoglobin', '', '14.1', 'g/L', '11.00-15.00', '14.1', 'g/L', '110-150', 'HGB', 107651, 1, '0006261', 'Complete Blood Count', '2021-04-14 05:22:47', '271489', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC', 1);
INSERT INTO `result` VALUES ('Hematocrit', '', '0.41', 'vol%', '0.37-0.47', '0.41', 'vol%', '0.37-0.47', 'HCT', 107652, 2, '0006261', 'Complete Blood Count', '2021-04-14 05:22:47', '271489', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC', 1);
INSERT INTO `result` VALUES ('MCV', 'L', '78.1', 'fL', '80.00-96.00', '78.1', 'fL', '80-96', 'MCV', 107653, 11, '0006261', 'Complete Blood Count', '2021-04-14 05:22:47', '271489', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC', 1);
INSERT INTO `result` VALUES ('MCH', 'L', '26.9', 'pg', '27.00-33.00', '26.9', 'pg', '27-33', 'MCH', 107654, 12, '0006261', 'Complete Blood Count', '2021-04-14 05:22:47', '271489', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC', 1);
INSERT INTO `result` VALUES ('MCHC', '', '34.5', 'g/L', '33.40-35.50', '34.5', 'g/L', '33-36', 'MCHC', 107655, 13, '0006261', 'Complete Blood Count', '2021-04-14 05:22:47', '271489', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC', 1);
INSERT INTO `result` VALUES ('Platelet Count', '', '224,000', 'x10^3/uL', '100000.00-300000.00', '224', 'x10^3/uL', '100,000-300,000', 'PLT', 107656, 5, '0006261', 'Complete Blood Count', '2021-04-14 05:22:47', '271489', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC', 1);
INSERT INTO `result` VALUES ('Neutrophils', '', '0.58', '', '0.50-0.70', '0.6', '', '1-1', 'NEU_P', 107657, 6, '0006261', 'Differential Count', '2021-04-14 05:22:47', '271489', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC', 1);
INSERT INTO `result` VALUES ('Lymphocytes', '', '0.36', '', '0.20-0.40', '0.4', '', '0-0', 'LYM_P', 107658, 9, '0006261', 'Differential Count', '2021-04-14 05:22:47', '271489', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC', 1);
INSERT INTO `result` VALUES ('Monocytes', '', '0.06', '', '0.03-0.12', '0.1', '', '0-0', 'MON_P', 107659, 10, '0006261', 'Differential Count', '2021-04-14 05:22:47', '271489', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC', 1);
INSERT INTO `result` VALUES ('Eosinophils', 'L', '0.00', '', '0.01-0.05', '0.0', '', '0-0', 'EOS_P', 107660, 7, '0006261', 'Differential Count', '2021-04-14 05:22:47', '271489', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC', 1);
INSERT INTO `result` VALUES ('Basophils', '', '0.00', '', '0.00-0.01', '0.0', '', '0-0', 'BAS_P', 107661, 8, '0006261', 'Differential Count', '2021-04-14 05:22:47', '271489', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC', 1);

INSERT INTO `result` VALUES ('White Blood Cells', '', '28.04', 'x10^9/L', '', '28.0', 'x10^9/L', '', 'WBC', 107664, 4, '0006261', 'Complete Blood Count', '2021-04-14 05:24:18', '271472', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC+BType', 1);
INSERT INTO `result` VALUES ('Red Blood Cells', '', '5.18', 'x10^12/L', '', '5.18', 'x10^12/L', '', 'RBC', 107665, 3, '0006261', 'Complete Blood Count', '2021-04-14 05:24:18', '271472', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC+BType', 1);
INSERT INTO `result` VALUES ('Hemoglobin', '', '17.7', 'g/L', '', '17.7', 'g/L', '', 'HGB', 107666, 1, '0006261', 'Complete Blood Count', '2021-04-14 05:24:18', '271472', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC+BType', 1);
INSERT INTO `result` VALUES ('Hematocrit', '', '0.50', 'vol%', '', '0.50', 'vol%', '', 'HCT', 107667, 2, '0006261', 'Complete Blood Count', '2021-04-14 05:24:18', '271472', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC+BType', 1);
INSERT INTO `result` VALUES ('MCV', '', '96.1', 'fL', '', '96.1', 'fL', '', 'MCV', 107668, 11, '0006261', 'Complete Blood Count', '2021-04-14 05:24:18', '271472', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC+BType', 1);
INSERT INTO `result` VALUES ('MCH', '', '34.2', 'pg', '', '34.2', 'pg', '', 'MCH', 107669, 12, '0006261', 'Complete Blood Count', '2021-04-14 05:24:18', '271472', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC+BType', 1);
INSERT INTO `result` VALUES ('MCHC', '', '35.5', 'g/L', '', '35.5', 'g/L', '', 'MCHC', 107670, 13, '0006261', 'Complete Blood Count', '2021-04-14 05:24:18', '271472', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC+BType', 1);
INSERT INTO `result` VALUES ('Platelet Count', '', '258,000', 'x10^3/uL', '', '258', 'x10^3/uL', '', 'PLT', 107671, 5, '0006265', 'Complete Blood Count', '2021-04-14 05:24:18', '271472', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC+BType', 1);
INSERT INTO `result` VALUES ('Neutrophils', '', '0.80', '', '', '0.8', '', '', 'NEU_P', 107672, 6, '0006261', 'Differential Count', '2021-04-14 05:24:18', '271472', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC+BType', 1);
INSERT INTO `result` VALUES ('Lymphocytes', '', '0.13', '', '', '0.1', '', '', 'LYM_P', 107673, 9, '0006261', 'Differential Count', '2021-04-14 05:24:18', '271472', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC+BType', 1);
INSERT INTO `result` VALUES ('Monocytes', '', '0.06', '', '', '0.1', '', '', 'MON_P', 107674, 10, '0006261', 'Differential Count', '2021-04-14 05:24:18', '271472', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC+BType', 1);
INSERT INTO `result` VALUES ('Eosinophils', '', '.01', '', '', '0.0', '', '', 'EOS_P', 107675, 7, '0006261', 'Differential Count', '2021-04-14 05:24:18', '271472', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC+BType', 1);
INSERT INTO `result` VALUES ('Basophils', '', '0.00', '', '', '0.0', '', '', 'BAS_P', 107676, 8, '0006261', 'Differential Count', '2021-04-14 05:24:18', '271472', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC+BType', 1);

This is the query that I used.

SET SESSION group_concat_max_len = 1000000;

SELECT
    GROUP_CONCAT(DISTINCT
        CAST(CONCAT('IFNULL((CASE WHEN `date` = ''', `date`, ''' THEN `measurement` END), NULL) AS ', `date`, '') AS CHAR)
     )INTO @sql
FROM
    `result` ORDER BY `date` DESC;
    
SET @sql = CONCAT('SELECT patient_id, universal_id AS `Parameter`, ', CAST(@sql as CHAR), ' 
                                    FROM result 
                                     GROUP BY universal_id ORDER BY order_no');
                                     
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Thank you!

Solution

After review the links then was send in the comment with some tweaks, I had it to work. Here’s the code that i used for future reference.

CREATE DEFINER=`root`@`localhost` PROCEDURE `delta_check`(IN PID VARCHAR(50), IN Section VARCHAR(50), IN SubSection VARCHAR(50))
BEGIN
    SET @PID = PID;
    SET @Section = Section;
    SET @SubSection = SubSection;
    
    SET SESSION group_concat_max_len = 1000000;
    
    SELECT GROUP_CONCAT(DISTINCT CAST(CONCAT("MAX(CASE WHEN DATE_FORMAT(`date`, '%y-%m-%d %H:%i') = '", DATE_FORMAT(`date`, '%y-%m-%d %H:%i'), "' THEN `measurement` END) AS `", DATE_FORMAT(`date`, '%y-%M-%d %H:%i'), "`") AS CHAR)) FROM result WHERE `patient_id` = PID AND section = Section AND sub_section = SubSection INTO @sql;
    
    SET @sql = CONCAT("SELECT result.universal_id AS Parameter, specimen.si_unit AS Unit, reference_range.si_range, result.test_code AS `TestCode`, ", @sql, ", result.`section` AS Section, result.`sub_section` AS SubSection
                                        FROM result 
                                        LEFT JOIN specimen ON result.test_code = specimen.test_code
                                        LEFT JOIN reference_range ON result.test_code = reference_range.test_code
                                        WHERE result.`patient_id` = ? AND result.section = ? AND result.sub_section = ? GROUP BY result.test_code ORDER BY specimen.order_no ASC");

    PREPARE stmt FROM @sql;
    EXECUTE stmt USING @PID, @Section, @SubSection;
    DEALLOCATE PREPARE stmt;
END

Cheers you all for the help.

Thank you!

Answered By – ardie pagulayan

Answer Checked By – Willingham (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.