我的SQL将行转换为列 [英] My SQL Convert Row to Column

查看:84
本文介绍了我的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

推荐答案

一般这样的行旋转列称为旋转。但是你在这里指定的与它无关。



你需要一些更复杂的查询:

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:
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将行转换为列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆