My SQL Convert Row to Column

Question

0.00/5 (No votes)

See more:

Hi,

I have come to the scenario in which I have to convert row to column using MySQL Query.
But I haven't find any good way to do it.

There are two tables (Table1 and Table2) as mentioned below and using MySQL query I would like to get final result as mentioned in Result Data.

Table1 :

ID|FirstName |LastName
----------------------------------
1 |aaa |aaa
2 |bbb |bbb
3 |ccc |ccc

Table2 :

Id|EmployeeId |TypeId |TypeName
--|------------|-------------------------
1 |1 |1 |aaa_admin
2 |1 |0 |aaa_employee
3 |2 |0 |bbb_employee
4 |3 |1 |ccc_admin

Result Data :

Id|FirstName|AdminName |EmployeeName
--|---------|-----------|-----------------------------
1 |aaa |aaa_admin |aaa_employee
2 |bbb |null |bbb_employee
3 |ccc |ccc_admin |null

can any one suggest me how to do so ?

Regards,
CodeBlack

Posted 27-Aug-13 22:30pm

CodeBlack

Updated 27-Aug-13 22:34pm

v2

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Zoltán Zörgő** · Answer 1 · 2013-08-27T23:39:00

In general such rotation of rows to columns is called pivoting. But what you specified here has nothing to do with it.

You need a little bit more complex query:

SQL

select 
a.id, 
a.firstname, 
a.lastname, 
(select b.typename from table2 b where b.employeeid=a.id and b.typeid=1) as adminname  
(select b.typename from table2 b where b.employeeid=a.id and b.typeid=0) as employeename  
from table1 a

My SQL Convert Row to Column

1 solution

Solution 1

Add your solution here

Preview 0

My SQL Convert Row to Column

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us