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?

How are first three rows related? Is there any field like `ID` etc? — Himanshu, Aug 08 '13 at 10:38
Try to google "MySQL Pivot". For Example: [MySQL pivot table query with dynamic columns](http://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns), [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) — valex, Aug 08 '13 at 10:38
@user2664042 - I am not talking about PK. But how will you decide that `Name1` and `Address1` will come in the same row? How will you group them? Like this: http://sqlfiddle.com/#!2/b3b8c — Himanshu, Aug 08 '13 at 10:41
You're missing `entity` from your EAV model, but assuming you have that, consider handling issues of data display at the application level/presentation layer (e.g. with a bit of PHP acting on a simple, ordered array) — Strawberry, Aug 08 '13 at 10:41
@hims056. yes the schema you wrote in fiddle is ok.can you provide me solution based on that — user2664042, Aug 08 '13 at 10:56

score 3 · Answer 1 · answered Aug 08 '13 at 10:57

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

score 0 · Answer 2 · answered Aug 08 '13 at 10:41

0

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.

answered Aug 08 '13 at 10:41

noz

1,863
13
14

Get ouput result as column in SQL

2 Answers2

See this SQLFiddle