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 |
How to convert MySQL rows to a column of 5
It is a bit ugly... but possible. You can use window functions - but you need a column that defines the ordering of the rows, I assumed id
.
select
max(case when rn % 5 = 0 then file end) as filea,
max(case when rn % 5 = 1 then file end) as fileb,
max(case when rn % 5 = 2 then file end) as filec,
max(case when rn % 5 = 3 then file end) as filed,
max(case when rn % 5 = 4 then file end) as filee
from (
select t.*, row_number() over(order by id) - 1 rn
from mytable t
) t
group by floor(rn / 5)
Demo on DB Fiddle:
filea | fileb | filec | filed | filee
:---- | :----- | :----- | :---- | :----
B.jpg | X.jpg | H.png | C.png | A.gif
G.pdf | Y.docx | U.jpeg | null | null
How can I return pivot table output in MySQL?
This basically is a pivot table.
A nice tutorial on how to achieve this can be found here: http://www.artfulsoftware.com/infotree/qrytip.php?id=78
I advise reading this post and adapt this solution to your needs.
Update
After the link above is currently not available any longer I feel obliged to provide some additional information for all of you searching for mysql pivot answers in here. It really had a vast amount of information, and I won't put everything from there in here (even more since I just don't want to copy their vast knowledge), but I'll give some advice on how to deal with pivot tables the sql way generally with the example from peku who asked the question in the first place.
Maybe the link comes back soon, I'll keep an eye out for it.
The spreadsheet way...
Many people just use a tool like MSExcel, OpenOffice or other spreadsheet-tools for this purpose. This is a valid solution, just copy the data over there and use the tools the GUI offer to solve this.
But... this wasn't the question, and it might even lead to some disadvantages, like how to get the data into the spreadsheet, problematic scaling and so on.
The SQL way...
Given his table looks something like this:
CREATE TABLE `test_pivot` (
`pid` bigint(20) NOT NULL AUTO_INCREMENT,
`company_name` varchar(32) DEFAULT NULL,
`action` varchar(16) DEFAULT NULL,
`pagecount` bigint(20) DEFAULT NULL,
PRIMARY KEY (`pid`)
) ENGINE=MyISAM;
Now look into his/her desired table:
company_name EMAIL PRINT 1 pages PRINT 2 pages PRINT 3 pages
-------------------------------------------------------------
CompanyA 0 0 1 3
CompanyB 1 1 2 0
The rows (EMAIL
, PRINT x pages
) resemble conditions. The main grouping is by company_name
.
In order to set up the conditions this rather shouts for using the CASE
-statement. In order to group by something, well, use ... GROUP BY
.
The basic SQL providing this pivot can look something like this:
SELECT P.`company_name`,
COUNT(
CASE
WHEN P.`action`='EMAIL'
THEN 1
ELSE NULL
END
) AS 'EMAIL',
COUNT(
CASE
WHEN P.`action`='PRINT' AND P.`pagecount` = '1'
THEN P.`pagecount`
ELSE NULL
END
) AS 'PRINT 1 pages',
COUNT(
CASE
WHEN P.`action`='PRINT' AND P.`pagecount` = '2'
THEN P.`pagecount`
ELSE NULL
END
) AS 'PRINT 2 pages',
COUNT(
CASE
WHEN P.`action`='PRINT' AND P.`pagecount` = '3'
THEN P.`pagecount`
ELSE NULL
END
) AS 'PRINT 3 pages'
FROM test_pivot P
GROUP BY P.`company_name`;
This should provide the desired result very fast. The major downside for this approach, the more rows you want in your pivot table, the more conditions you need to define in your SQL statement.
This can be dealt with, too, therefore people tend to use prepared statements, routines, counters and such.
Some additional links about this topic:
- http://anothermysqldba.blogspot.de/2013/06/pivot-tables-example-in-mysql.html
- http://www.codeproject.com/Articles/363339/Cross-Tabulation-Pivot-Tables-with-MySQL
- http://datacharmer.org/downloads/pivot_tables_mysql_5.pdf
- https://codingsight.com/pivot-tables-in-mysql/
MYSQL Transpose the rows of the result of a select in column (Only 1 column result)
You can do this with a dynamic statement and PREPARE STATEMENT
like this:
SELECT CONCAT("SELECT "
, GROUP_CONCAT( column_name )
, " FROM MYTABLE"
, " WHERE 1=1") into @myQuery
FROM information_schema.columns
WHERE table_name = 'MYTABLE'
AND TABLE_SCHEMA = 'textil';
SELECT @myQuery;
PREPARE stmt FROM @MYQUERY;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
sample
MariaDB [textil]> SELECT * from MYTABLE;
+----+------+------+------+
| T0 | T1 | T2 | T3 |
+----+------+------+------+
| 1 | 2 | 4 | 8 |
| 2 | 4 | 8 | 16 |
+----+------+------+------+
2 rows in set (0.00 sec)
MariaDB [textil]> SELECT CONCAT("SELECT "
-> , GROUP_CONCAT( column_name )
-> , " FROM MYTABLE"
-> , " WHERE 1=1") into @myQuery
-> FROM information_schema.columns
-> WHERE table_name = 'MYTABLE'
-> AND TABLE_SCHEMA = 'textil';
Query OK, 1 row affected (0.01 sec)
MariaDB [textil]>
MariaDB [textil]> SELECT @myQuery;
+-------------------------------------------+
| @myQuery |
+-------------------------------------------+
| SELECT T0,T1,T2,T3 FROM MYTABLE WHERE 1=1 |
+-------------------------------------------+
1 row in set (0.00 sec)
MariaDB [textil]>
MariaDB [textil]> PREPARE stmt FROM @MYQUERY;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
MariaDB [textil]> EXECUTE stmt;
+----+------+------+------+
| T0 | T1 | T2 | T3 |
+----+------+------+------+
| 1 | 2 | 4 | 8 |
| 2 | 4 | 8 | 16 |
+----+------+------+------+
2 rows in set (0.01 sec)
MariaDB [textil]>
MariaDB [textil]> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)
MariaDB [textil]>
Select specific rows and columns from an MySQL database
cmd.CommandText = "insert into std_att (nibm_id, nic, name, batch) SELECT nibm_id, nic, name, batch FROM `std_info` where nibm_id = '" + textBox1.Text + "%'";
There are obviously a lot better ways to get this done, and the above is vulnerable to SQL injections etc, but I can understand if this is for a simple quick piece of work.
Like some of the users have said, don't have duplicate columns in std_att
table since they are already in the main table std_info
I would also try to find the nibm_id
before the insert bit, to validate in-app and not let the db freak out on it's side (if nibm_id
can't be found)
Related Topics
How to Use Group by to Concatenate Strings in SQL Server
Is There Any Rule of Thumb to Construct SQL Query from a Human-Readable Description
Oracle Sql: Update a Table With Data from Another Table
How to Select the Nth Row in a SQL Database Table
How to Schedule a Job to Run a SQL Query Daily
MySQL Cannot Add Foreign Key Constraint
How to Spool to a CSV Formatted File Using Sqlplus
In VS or in the SQL Where Clause
Selecting Count(*) With Distinct
Select Group of Rows That Match All Items in a List
SQL to Linq With Multiple Join, Count and Left Join
SQL Join - Where Clause Vs. on Clause
SQL Join: Is There a Difference Between Using, on or Where
How to Reset a Sequence in Oracle
Auto Increment After Delete in MySQL
How to Query Using Fields Inside the New Postgresql Json Datatype