SELECT * FROM B LEFT JOIN A WHERE A.id=B.a_id AND A.id=1

假设 id 列 table a 是一个 primary key 列和 table b 具有的复合唯一键 a_id , key 列组合或没有重复项即使没有此类约束，相关子查询也可以用作：

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;

使用时的用例 max() ```
select a.name,
max(case when key='looks' then value end) as looks,
max(case when key='lazy' then value end) as yes,
max(case when key='car' then value end) as car,
max(case when key='email' then value end) as email,
tablea a join tableb b on a.id=b.a_id
group by a.name

试试这个

WITH CTE_A AS (
   SELECT
      A.id,
      A.name,
      B.key,
      B.value
   FROM  A
    INNER JOIN B ON A.id = B.a_id
)
SELECT *
FROM
   CTE_A
   PIVOT (max(value) FOR key IN (looks, lazy, car,phone,email)) P
;

如果键和值是动态的，请使用下面的

DECLARE @colsToPivot AS NVARCHAR(MAX),
        @sqlStmt  AS NVARCHAR(MAX)
select @colsToPivot = STUFF((SELECT distinct ',' + QUOTENAME(key) 
                    from B
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @sqlStmt = 'WITH CTE_A AS (
       SELECT
          A.id,
          A.name,
          B.key,
          B.value
       FROM  A
        INNER JOIN B ON A.id = B.a_id
    )
    SELECT *
    FROM
       CTE_A
       PIVOT (max(value) FOR key IN (' + @colsToPivot +')) P'

execute(@sqlStmt)

您可以尝试使用mysql动态pivot来满足您的期望。
使用条件聚合函数进行透视
准备sql语句并用于执行sql EXECUTE stmt; 动态地。

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when `key`= ''',
      `key`,
      ''' then `value` end) AS ',
      `key`
    )
  ) INTO @sql
FROM A join B on a.id=b.a_id
WHERE b.a_id = 1;

SET @sql = CONCAT('select a.id,a.name, ', @sql, ' 
                   FROM A join B on a.id=b.a_id
                    WHERE b.a_id = 1
                    group by a.name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

sqlfiddle公司
结果

id  name    looks       laz   car   email
1   sorabh  handsome    yes   honda some@ccid.com

参考
动态数据透视表