MySQL - Rows to Columns

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_noQno1Qno2Qno3
21345100102103
21346100144null
21567233455null

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



Leave a reply



Submit