MySQL Transpose via JavaScript

Uses a web form to accept list of words representing fields to be transposed

by Kris Tyte

HTML

<div>
    <div>
        <p>Input Codes:</p>
        <textarea id="input" title="Input Codes" cols="15" rows="30">PSI&#13;&#10;SIZE&#13;&#10;CFM&#13;&#10;BELT&#13;&#10;RPM</textarea>
    </div>
    <div>
        <button onclick="process()">Process</button>
    </div>
    <div>
        <p>SQL Output</p>
        <textarea id="sql_output" title="SQL Output" cols="30" rows="30">SQL output will appear here</textarea>
    </div>
</div>

CSS

div {
            text-align: center;
        }
        
        div > div {
            display: inline-block;
            vertical-align: top;
        }
        
        textarea {
            border: 2px solid
        }
        
        p {
            margin: 0;
        }
        
        button {
            margin: 50px 5px 0 5px;
        }

JavaScript

function process() {
    var input = document.getElementById("input");
    var sql_output = document.getElementById("sql_output");
    var codes = input.value.trim().split(/\n/); // gets rid of any extra leading or trailing whitespace
    var output = ["SELECT", "    parent_id AS `ID`,"];
    var line = "";
    for (var i = 0; i < codes.length; i++) {
        if (/\S+/.test(codes[i])) { // lines with something other than whitespace
            line = ("    MAX(CASE WHEN `code` = '" + codes[i].trim() + "'  THEN `value` ELSE NULL END) AS `" + codes[i].trim() + "`");
            if (i < codes.length - 1) line = line + ","; // don't add , to last line
            output.push(line);
        }
    }
    output.push("FROM\n    `child`\nGROUP BY\n    `parent_id`");
    sql_output.value = output.join("\n");
}