-2

I had the following sample data

------------------------------|------|-
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
---------|----------------|-----------|-----------|---------|---------|---------

Please help me to get my problem solve.

Poornima
  • 94
  • 1
  • 8
  • 2
    What you have tried yet ? – Mayank Pandeyz Feb 23 '17 at 06:24
  • can you provide table details ? – chirag satapara Feb 23 '17 at 06:25
  • http://stackoverflow.com/questions/3392956/sql-how-to-transpose or http://stackoverflow.com/questions/16568228/how-to-transpose-mysql-table-rows-into-columns – web-nomad Feb 23 '17 at 06:26
  • Thats called paivot table. [This SO Link](http://stackoverflow.com/questions/7674786/mysql-pivot-table) can help you. – RRajani Feb 23 '17 at 06:28
  • @poornima , this school list is static or dynamic? – chirag satapara Feb 23 '17 at 06:45
  • Without pivot table i need to carry out this one – Poornima Feb 23 '17 at 06:46
  • @Poornima this is called pivoting, so you cannot fo this without pivot tables. The duplicate link I provided describes both static and dynamic pivoting in mysql. Pls slso consider doing this transformation in your application. I may be more efficient. – Shadow Feb 23 '17 at 06:49
  • I am getting all datas dynamically from database .For pivot table it seems to use static data.Is pivot table is possible to handle dynamic data?If possible,please send some link for pivot table with dynamic data – Poornima Feb 23 '17 at 06:58
  • whats wrong with keeping the same sql query then having your "application" provide you with the table... – barudo Feb 23 '17 at 07:07

1 Answers1

0

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.

If this is your table structure :

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.

chirag satapara
  • 1,947
  • 1
  • 15
  • 26