Xtykutl

I have two tables, A and B

A ( id, name )

B ( a_id, key, value )

table with some values

**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    |    some@ccid.com

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

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

and result must be something like

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

  id   |   name   |    looks   |   lazy  |    car   |   email

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

   1   |  sorabh  |   handsome |   yes   |  honda   | some@ccid.com 

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

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;

You can try to use mysql dynamic pivot to make you expect.

Do pivot with condition aggregate function

Prepared your SQL statements and use to execute your SQL EXECUTE stmt; dynamically.

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

Result

id  name    looks       laz   car   email

1   sorabh  handsome    yes   honda some@ccid.com

Refer

Dynamic pivot tables

use case when with 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

Try this

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

;

if the key and value are dynamic use the below

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)

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