我该如何联接两个表并动态进行数据透视 [英] How can I do Join two tables and do Pivot dynamically

问题描述

我有两个表A和B

A(id，name)
B(a_id，键，值)

A ( id, name )
B ( a_id, key, value )

带有一些值的表

**A table**

-----------------------------
   id        |      name
-----------------------------
    1        |      sorabh
    2        |      john
-----------------------------

**B table**

-------------------------------------------------
     a_id    |     key     |     value
-------------------------------------------------
      1      |    looks    |    handsome
      1      |    lazy     |    yes
      1      |    car      |    honda
      2      |    phone    |    948373221
      1      |    email    |    [e-mail]
-------------------------------------------------

现在我要实现的是一个跟踪，具有单个查询，内部联接，交叉联接等.

Now what i am trying to achieve is a follow, with single query, inner join, cross join, etc.

SELECT * FROM A
CROSS JOIN B WHERE A.id=1

结果必须类似于

--------------------------------------------------------------------
  id   |   name   |    looks   |   lazy  |    car   |   email
--------------------------------------------------------------------
   1   |  sorabh  |   handsome |   yes   |  honda   | [e-mail] 
--------------------------------------------------------------------

推荐答案

假定table a的id列是primary key列，并且table b具有用于a_id，key的复合唯一键列组合或没有重复项(即使没有这样的约束)，相关的子查询也可以用作:

Assuming the id column of table a is a primary key column and table b has a composite unique key for a_id, key column-combination or has no duplicates even if there's no such constraint, a correlated subquery may be used as :

select a.*,
       ( select value from b where b.a_id = a.id and b.key = 'looks' ) as looks,
       ( select value from b where b.a_id = a.id and b.key = 'lazy' ) as lazy,
       ( select value from b where b.a_id = a.id and b.key = 'car' ) as car,
       ( select value from b where b.a_id = a.id and b.key = 'email' ) as email
  from a
 where a.id = 1;

这篇关于我该如何联接两个表并动态进行数据透视的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！