0

I have a single table as below:

+-----------+-----------+--------+
|  ID       | Name      | Format |
+-----------+-----------+--------+
| 1         | Rama      | Text   |
+-----------+-----------+--------+
| 2         | Sita      | Text   |
+-----------+-----------+--------+
| 1         | Raja      | Text   |
+-----------+-----------+--------+
| 2         | Bheem      | Text   |
+-----------+-----------+--------+

I want below output from above table:

+-----------+-----------+
|  ID_1     | ID_2      |
+-----------+-----------+
| Rama      | Sita      |
+-----------+-----------+
| Raja      | Bheem     |
+-----------+-----------+

Can anyone give me mysql query to do this?

Thank you in advance.

rks
  • 113
  • 6
  • How do you know which id 1 to pair with which id 2 record? What have you tried so far? – Shadow Oct 27 '15 at 07:33
  • I have written 2 separate query to extract the data. Like select Name as ID_1 from table1 where ID=1; Is it possible to get single query for this or do i need to join 2 subqueries. – rks Oct 27 '15 at 07:37
  • You have not answered my question. – Shadow Oct 27 '15 at 07:38
  • no need to pair.. I just need two columns, one for Id 1 and another for id 2 – rks Oct 27 '15 at 07:41
  • 2
    Possible duplicate of [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) – agad Oct 27 '15 at 07:42

1 Answers1

0

You can try this query.

SELECT  ID,
        MAX(IF(`ID` = 1, Name, NULL)) ID_1,
        MAX(IF(`ID` = 2, Name, NULL)) ID_2
FROM    Table
GROUP   BY ID

Follow this fiddle code which is similar to your requirement. http://sqlfiddle.com/#!2/70129/13

sandeepsure
  • 1,113
  • 1
  • 10
  • 17