Mysql query to dynamically convert rows to columns on the basis of two columns
If you had a known number of values for both order
and item
, then you could hard code the query into:
select id,
max(case when `order` = 1 then data end) order1,
max(case when `order` = 2 then data end) order2,
max(case when `order` = 3 then data end) order3,
max(case when item = 1 then price end) item1,
max(case when item = 2 then price end) item2,
max(case when item = 3 then price end) item3,
max(case when item = 4 then price end) item4
from tableA
group by id;
See Demo. But part of the problem that you are going to have is because you are trying to transform multiple columns of data. My suggestion to get the final result would be to unpivot the data first. MySQL does not have an unpivot function but you can use a UNION ALL to convert the multiple pairs of columns into rows. The code to unpivot will be similar to the following:
select id, concat('order', `order`) col, data value
from tableA
union all
select id, concat('item', item) col, price value
from tableA;
See Demo. The result of this will be:
| ID | COL | VALUE |
-----------------------
| 1 | order1 | P |
| 1 | order1 | P |
| 1 | order1 | P |
| 1 | item1 | 50 |
| 1 | item2 | 60 |
| 1 | item3 | 70 |
As you can see this has taken the multiple columns of order
/data
and item
/price
and convert it into multiple rows. Once that is completed, then you can convert the values back into columns using an aggregate function with a CASE:
select id,
max(case when col = 'order1' then value end) order1,
max(case when col = 'order2' then value end) order2,
max(case when col = 'order3' then value end) order3,
max(case when col = 'item1' then value end) item1,
max(case when col = 'item2' then value end) item2,
max(case when col = 'item3' then value end) item3
from
(
select id, concat('order', `order`) col, data value
from tableA
union all
select id, concat('item', item) col, price value
from tableA
) d
group by id;
See Demo. Finally, you need to convert the above code into a dynamic prepared statement query:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'max(case when col = ''',
col,
''' then value end) as `',
col, '`')
) INTO @sql
FROM
(
select concat('order', `order`) col
from tableA
union all
select concat('item', `item`) col
from tableA
)d;
SET @sql = CONCAT('SELECT id, ', @sql, '
from
(
select id, concat(''order'', `order`) col, data value
from tableA
union all
select id, concat(''item'', item) col, price value
from tableA
) d
group by id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
See SQL Fiddle with demo. This gives a result:
| ID | ORDER1 | ORDER2 | ORDER3 | ITEM1 | ITEM2 | ITEM3 | ITEM4 |
-------------------------------------------------------------------
| 1 | P | Q | (null) | 50 | 60 | 70 | (null) |
| 2 | P | (null) | S | 50 | 60 | (null) | 80 |
Mysql query to dynamically convert rows to columns
You can use GROUP BY
and MAX
to simulate pivot. MySQL also supports IF
statement.
SELECT ID,
MAX(IF(`order` = 1, data, NULL)) data1,
MAX(IF(`order` = 2, data, NULL)) data2
FROM TableA
GROUP BY ID
- SQLFiddle Demo
If you have multiple values of order
, dynamic SQL may be more appropriate so that you will not have to modify the query:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(`order` = ', `order`, ',data,NULL)) AS data', `order`)
) INTO @sql
FROM TableName;
SET @sql = CONCAT('SELECT ID, ', @sql, '
FROM TableName
GROUP BY ID');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
- SQLFiddle Demo
- SQLFiddle Demo (Another example)
OUTPUT OF BOTH QUERIES:
╔════╦═══════╦═══════╗
║ ID ║ DATA1 ║ DATA2 ║
╠════╬═══════╬═══════╣
║ 1 ║ P ║ S ║
║ 2 ║ R ║ Q ║
╚════╩═══════╩═══════╝
Convert Rows to Columns in MySQL Dynamically
You can't have dynamic columns, at least without dynamically generating the SQL. You can build the SQL in a stored procedure as per this answer
MySQL pivot table query with dynamic columns
Alternatively it might be simpler to do this in your application code by selecting the distinct subjects in one query and using that result set to build the SQL that retrieves the result set you are after. At least with the logic in the application code you have some idea of how many columns you will be seeing in the result set.
How to dynamically convert row to column in MySQL and display the results in Swing
Creating a Stored Procedure with a Prepared Statement
First, you must create a stored procedure using a prepared statement. I have not tested your code, but the stored procedure can be created using code like the following.
DROP PROCEDURE IF EXISTS execSql;
DELIMITER //
CREATE PROCEDURE execSql ()
BEGIN
SET @sql = CONCAT( ...
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;
Invoking a MySQL Stored Procedure using JDBC
JDBC offers a CallableStatement
class you can use to invoke stored procedures. You may check a tutorial for Calling MySQL Stored Procedures from JDBC.
// use CALL with the name of the stored procedure
String query = "{CALL execSql()}";
// use CallableStatement to obtain the ResultSet
CallableStatement stmt = conn.prepareCall(query)
ResultSet rs = stmt.executeQuery();
Showing the result in a Swing JTable
To display the data, you may create a TableModel
with the ResultSet. There are many classes/libraries that you can use to fill a TableModel with the resultset (such as this DbUtils or this JDBCTableModel).
// execute the query
String query = "{CALL execSql()}";
CallableStatement stmt = conn.prepareCall(query)
ResultSet rs = stmt.executeQuery();
// fill the TableModel with the results
TableModel normalTableModel = DBUtils.resultSetToTableModel(rs);
// create the Swing Table
JTable swingTable = new JTable (transposedTableModel);
Transposing a TableModel
Sometimes you want to transpose (convert rows to columns) the results ot a Query. Although MySQL does not support PIVOT/UNPIVOT, you can transpose the Swing's TableModel. I think transposing the TableModel is the better (simpler) alternative.
Basically, you can create a TableModel wrapper that transpose the results. You may check the Spring TableModel and the JideSoft TransposeTableModel. The code for a TransposedTableModel
may be something like the following...
public TransposedTableModel extends AbstractTableModel {
private TableModel innerTableModel;
public TransposedTableModel(TableModel innerTableModel) {
this.innerTableModel = innerTableModel;
}
@Override
public int getRowCount() {
return innerTableModel.getColumnCount();
}
@Override
public int getColumnCount() {
return innerTableModel.getRowCount();
}
Override
public Object getValue(int row, int column) {
return innerTableModel.getValue(column, row);
}
};
Then, you can use a "normal" SQL, fill a TableModel with the result and the use the transposed TableModel.
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM table1");
TableModel normalTableModel = DBUtils.resultSetToTableModel(rs);
TableModel transposedTableModel = new TransposedTableModel(normalTableModel);
JTable swingTable = new JTable (transposedTableModel);
How to convert row to column in MySQL
Unfortunately, MySQL does not have PIVOT function, so in order to rotate data from rows into columns you will have to use a CASE expression along with an aggregate function.
Schema and insert statements:
create table student_det(ID int, Roll_No int, QNo int);
insert into student_det values(1 ,21345, 100);
insert into student_det values(2 , 21345 , 102);
insert into student_det values(3 , 21345 , 103);
insert into student_det values(4 , 21346 , 100);
insert into student_det values(5 , 21346 , 144);
insert into student_det values(6 , 21567 , 233);
insert into student_det values(7, 21567 , 455);
Query:
with cte as
(
Select Roll_No, QNo,
row_number() over(partition by Roll_No order by Roll_No) ColumnSequence
from student_det
)
select roll_no,
max(case when columnsequence=1 then QNo end)Qno1,
max(case when columnsequence=2 then QNo end)Qno2,
max(case when columnsequence=3 then QNo end)Qno3
from cte
group by roll_no
Output:
roll_no | Qno1 | Qno2 | Qno3 |
---|---|---|---|
21345 | 100 | 102 | 103 |
21346 | 100 | 144 | null |
21567 | 233 | 455 | null |
Transpose rows into columns when rows are dynamic -MYSQL
You can use GROUP_CONCAT and Prepared statements to handle dynamic number of fields in MySQL Pivot.
Please find query below with some test data.
Prepare Data
CREATE TABLE Meeting
(
ID INT,
Meeting_id INT,
field_key VARCHAR(100),
field_value VARCHAR(100)
);
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (1, 1,'first_name' , 'danyal');
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (2, 1,'last_name' , 'sandeelo');
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (3, 1,'occupation' , 'engineer');
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (4,2,'first_name' , 'John');
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (5,2,'last_name' , 'Matthew');
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (6,2,'occupation' , 'engineer');
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (7,2,'field4' , 'xyz');
SQL Query
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'max(case when field_key = ''',
field_key,
''' then field_value end) ',
field_key
)
) INTO @sql
FROM
Meeting;
SET @sql = CONCAT('SELECT Meeting_id, ', @sql, '
FROM Meeting
GROUP BY Meeting_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
MYSQL convert row to column
This is only slightly more complicated than a regular pivot because you need a condition on two columns:
select student_name,
max(case when assessment_type = 'QUIZ' and assesment_no = 1 then marks end) as quiz1,
max(case when assessment_type = 'QUIZ' and assesment_no = 2 then marks end) as quiz2
from student_marks
group by student_name;
You can expand this into dynamic SQL as:
SET @condition = 'MAX(CASE WHEN assessment_type = ''@at'' AND assement_no = @an THEN assessment_marks END) as @at@an';
SELECT @conditions := GROUP_CONCAT(REPLACE(REPLACE(@condition, '@at', assessment_type), '@an', assessment_no))
FROM studentmarks;
SET @sql = CONCAT('
SELECT student_name, ', @conditions, '
FROM studentmarks
GROUP BY student_name');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Related Topics
How to Script Out Stored Procedures to Files
Set Time Portion of a Datetime Variable
How to Convert Int to Date in SQL Server 2008
SQL Server Bulk Insert CSV with Data Having Comma
SQL How to Convert Row with Date Range to Many Rows with Each Date
Rails Virtual Attribute Search or SQL Combined Column Search
How to Input a Nodejs Variable into an SQL Query
Alter Table Then Update in Single Statement
Why Select Top Clause Could Lead to Long Time Cost
What Is the Ms SQL Server Capability Similar to the MySQL Field() Function
Is Order in a Subquery Guaranteed to Be Preserved
Oracle: SQL Query That Returns Rows with Only Numeric Values
SQL Server Equivalent of MySQL's Now()
How to Connect to SQL Express "Error: 26-Error Locating Server/Instance Specified)
Calling a Stored Procedure in Oracle with in and Out Parameters
How to Find the Record in a Table That Contains the Maximum Value