Get ouput result as column in SQL

Question

My Table looks something like this.

 +---------+---------------------
| ELEMENT_NAME | ELEMENT_VALUE | 
+--------------+---------------+
| NAME         | NAME1         |
| Address      | Address1      |
| City         | City1         |
| NAME         | NAME2         |
| Address      | Address2      |
| City         | City1         |
+-------------------------------

I need output something like this

+---------+---------------------
| NAME  |Address    | City     |
+--------------+---------------+
| NAME1 | Address1  | City1    |
| NAME2 | Address2  | City2    |
+-------------------------------

Note :- Name, Address, City is just an example. It can be anything

Can anybody help me out?

Answer 1

You can use GROUP_CONCAT() for that:

SELECT ID
   ,GROUP_CONCAT(CASE WHEN ELEMENT_NAME = 'NAME' 
                 THEN ELEMENT_VALUE ELSE NULL END) AS `NAME`
   ,GROUP_CONCAT(CASE WHEN ELEMENT_NAME = 'Address' 
                 THEN ELEMENT_VALUE ELSE NULL END) AS `Address`
   ,GROUP_CONCAT(CASE WHEN ELEMENT_NAME = 'City' 
                 THEN ELEMENT_VALUE ELSE NULL END) AS `City`
  FROM Table1
 GROUP BY ID;

Dynamic query (in case you don't know the number of ELEMENT_NAME or there are too many ELEMENT_NAME):

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'GROUP_CONCAT(CASE WHEN `ELEMENT_NAME` = ''',
      `ELEMENT_NAME`,
      ''' THEN ELEMENT_VALUE ELSE NULL END) AS `',
      `ELEMENT_NAME`, '`'
    )
  ) INTO @sql
FROM Table1;

SET @sql = CONCAT('SELECT ID, ', @sql,'
                     FROM Table1
                    GROUP BY ID
                  ');

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

Output:

| ID |  NAME |  ADDRESS |  CITY |
---------------------------------
|  1 | NAME1 | Address1 | City1 |
|  2 | NAME2 | Address2 | City1 |

See this SQLFiddle