需要将列转换为MYSQL中的行 [英] Need to Convert Columns into Rows in MYSQL

问题描述

我有以下示例数据

------------------------------|------|-
Sishya School                 |  3   | 
------------------------------|------|--
Chettinad Vidhyashram         |  2   |
------------------------------|------|--
Asan Memorial School          |  8   |
------------------------------|------|--
Maharishi Vidhya  Mandir      |  1   | 
------------------------------|------|--
PSBB Memorial School          |  4   |
------------------------------|------|--
D.V.S Public School           |  2   |
------------------------------|------|--
St.Mary’s School              |  5   | 
------------------------------|------|--

我想将列转换为行，以便表应如下所示，

I want to convert columns into Rows so that the table should look like the following,

---------|----------------|-----------|-----------|---------|---------|-------
Sishya   |     Chettinad  |   Asan    |  Maharishi| PSBB    |   D.V.S |St.Mary’s
School   |    Vidhyashram |   Memorial|  Vidhya   | Memorial|   Public| School 
         |                |    School |  Mandir   |  School |   School|
---------|----------------|-----------|-----------|---------|---------|-------
         |                |           |           |         |         | 
3        |        2       |      8    |       1   |       4 |      2  |     5
---------|----------------|-----------|-----------|---------|---------|---------                                            

请帮助我解决问题.

推荐答案

下面的解决方案可以帮助您解决问题，您需要根据表结构进行一些更改.

May be below solution help you to solve your problem , you need to do some changes as per your table structure.

对于此解决方案，您必须创建一个存储过程.

For this solution you have to make a stored procedure.

如果这是您的表结构:

CREATE TABLE `school` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(500) DEFAULT NULL,
  `value` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
SET FOREIGN_KEY_CHECKS=1;

如果上面的是您的表结构，则下面的解决方案有效.

Below solution is working if above is your table structure.

   SET SESSION group_concat_max_len = (2056 * 2056);

SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT
             CONCAT(
               'MAX(CASE WHEN school.name ="',m.name,'"'
                                ' THEN school.value END)"',m.name , '"'))
                                INTO @sql  
                                            from school as m;

SET @sql = CONCAT('SELECT value,',@sql,
                  ' FROM school');


PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

根据您的表结构进行更改.

Do changes as per your table structure.

此解决方案对于多表也很有帮助，希望这可以帮助您解决问题.

This solution is also helpful for multiple table ,I hope this may help you to solve your problem.

这篇关于需要将列转换为MYSQL中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！