CODEWITHSUNDEEP
mysqlsqlpivot
user2664042
user2664042

Reputation: 31

Get ouput result as column in SQL

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?

Upvotes: 3

Views: 74

Answers (2)

Himanshu
Himanshu

Reputation: 32602

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

Upvotes: 3

Neil Hampton
Neil Hampton

Reputation: 1883

With just ELEMENT_NAME and ELEMENT_VALUE this is pretty much impossible as you cannot tie up each record (i.e. which Address goes with which NAME element). The order of records in the database is not defined.

Upvotes: 0

Related Questions