我的SQL将行转换为列

问题描述

我来到了必须使用MySQL Query将行转换为列的场景。

但我找不到任何好方法。



如下所述有两个表（表1和表2）并使用MySQL查询我希望获得结果数据中提到的最终结果。



表1：



ID | FirstName | LastName

----------------------------------

1 | aaa | aaa

2 | bbb | bbb

3 | ccc | ccc



表2：



Id | EmployeeId | TypeId | TypeName

- | ------------ | -------------------------

1 | 1 | 1 | aaa_admin

2 | 1 | 0 | aaa_employee

3 | 2 | 0 | bbb_employee

4 | 3 | 1 | ccc_admin





结果数据：



Id | FirstName | AdminName | EmployeeName

- | --------- | ----------- | ----- ------------------------

1 | aaa | aaa_admin | aaa_employee

2 | bbb | null | bbb_employee

3 | ccc | ccc_admin | null





任何人都可以建议我怎么做这样做 ？



问候，

CodeBlack

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

推荐答案

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

这篇关于我的SQL将行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！