How to pivot table in MySql?-CodePudding

I have a table illustrated on the attached screen:

And data is presented:

As you can see it contains all fields from lines (value, column_id - is field type). Each field has line_index and column_index of position and column_id.

I want to get back all lines with all field names like:

line_index column_id_1  column_name1 column_id_2   column_name2   column_id_3   column_name3  
0          1             Age           2            Vasile         3              NY

More simply I need to build all fields(rows) to columns then to lines back.

Should I use Pivot and is it possible in MySQL?

Link to sqlfiddle

CodePudding user response：

You will need to group the results by line_index and conditionally transform rows to columns.

SELECT l.line_index, 
MAX(CASE WHEN ct.column_name = "NAME" THEN l.value ELSE NULL END) AS "NAME" ,
MAX(CASE WHEN ct.column_name = "AGE" THEN l.value ELSE NULL END) AS "AGE" ,
MAX(CASE WHEN ct.column_name = "ZUP" THEN l.value ELSE NULL END) AS "ZUP" 
FROM columns_types ct 
LEFT JOIN `lines` l ON l.column_id = ct.column_id
GROUP BY l.line_index;

Try it here. Also I did some changes in your schema which I felt didn't impact the data stored in the tables. You were adding redundant rows(perhaps) in your columns_types table. And by looking at the query, you know that you will have to build a MAX(CASE statement for each column, so its best if you first fetch it and then build the final query in some programming language.