How to fetch row value as a column name In Mysql

Question

I need help with below scenario. How can we get distinct values in rows as column Names dynamically?

I have data in below format..

 Sno  firstName Subject   Marks
 1     ABC         Eng      10
 2     PQR         Hindi    20
 3     LM          Telgu    20 
 4     LM          Hindi    20
 5     LM          Eng      39

I need output in below format.

Sno   FirstName    Eng    Hindi   Telgu
  1      ABC        10     Null    Null
  2     PQR        Null    20      Null
  3      LM         39     20       20

If one more subject is added, query should be written dynamic enough to include those values too..

How can Write Query for This?

you need to clarify your question a little more... is your question about getting the `distinct firstnames` from these tables? — The Fabio, Aug 22 '15 at 09:18
Make the question clear and also please try making a table format to display a table. Unsorted table data looks very bad and unreadable. — codewitharefin, Aug 22 '15 at 09:23
possible duplicate of [MySQL pivot row into dynamic number of columns](http://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns) or [Posts containing 'mysql dynamic pivot'](http://stackoverflow.com/search?q=mysql+dynamic+pivot). — Abecee, Aug 22 '15 at 11:27

score 0 · Answer 1 · edited May 23 '17 at 12:22

Hi there as Abecee told you in comment solution for your problem is pivoting table.

Here is query for your table

SELECT Sno, firstName, 
      SUM(CASE WHEN Subject = 'Eng' THEN Marks ELSE NULL END) AS Eng,
      SUM(CASE WHEN Subject = 'Hindi' THEN Marks ELSE NULL END) AS Hindy,
      SUM(CASE WHEN Subject = 'Telgu' THEN Marks ELSE NULL END) AS Telgu
FROM t1
GROUP BY firstName
ORDER BY Sno

And here is SQL Fiddle to see how it's work...

GL!

Edit:

Ok based on this bluefeet answer here is dynamics solution for your problem

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT 
        CONCAT('SUM(CASE WHEN Subject = ''',
                Subject,
               ''' THEN Marks ELSE NULL END) AS ',
               REPLACE(Subject, ' ', ''))) INTO @sql
FROM t1;

SET @sql = CONCAT('SELECT Sno, firstName, ', @sql, ' 
                  FROM t1
                  GROUP BY firstName
                  ORDER BY Sno');

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

First part of this

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT 
        CONCAT('SUM(CASE WHEN Subject = ''',
                Subject,
               ''' THEN Marks ELSE NULL END) AS ',
               REPLACE(Subject, ' ', ''))) INTO @sql
FROM t1;

is to dynamically create this part of first query I wrote

 SUM(CASE WHEN Subject = 'Eng' THEN Marks ELSE NULL END) AS Eng,
 SUM(CASE WHEN Subject = 'Hindi' THEN Marks ELSE NULL END) AS Hindy,
 SUM(CASE WHEN Subject = 'Telgu' THEN Marks ELSE NULL END) AS Telgu

the second part is to create prepared statement and to include dynamically created part into this

SELECT Sno, firstName, 
       -- here you add dynamically created part @sql
FROM t1
GROUP BY firstName
ORDER BY Sno

you do that with CONCAT in this part of code

SET @sql = CONCAT('SELECT Sno, firstName, ', @sql, ' 
                  FROM t1
                  GROUP BY firstName
                  ORDER BY Sno');

when you create that from that string you prepare statement and execute it

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

Hope that is a little bit clear now...

Here is SQL Fiddle so you can see that you can compare both query and see how it's work..

GL!

P.S. if you have any question fill free to ask in comment bellow

Thanks But i need to take row name as a column Name Dynamically....i Should Not be assigned alies Name... — user3751091, Aug 23 '15 at 15:48
@user3751091 Hi there I edit my answer and add dynamically generated query. Hope that help! — Aleksandar Miladinovic, Aug 23 '15 at 20:19

How to fetch row value as a column name In Mysql

1 Answers1