Converting Multiple rows into columns in MySQL

Question

I have two tables:

TableA

+-------+--------+
| Data  | Acc_No |
+-------+--------+
| Unix  |     10 |
| Linux |     20 |
+-------+--------+ 

TableB

+-----+----------+--------+
| Obj | Type     | Amount |
+-----+----------+--------+
|  10 | rev      | 100.00 |
|  10 | revision | 200.00 |
+-----+----------+--------+

I want the output like this

 +-------+--------+----------+--------+-----------+----------+
 | Data  | Acc_No | Type     | Amount | Type_1    | Amount_1 |
 +-------+--------+----------+--------+-----------+----------+
 | Unix  | 10     | rev      | 100    | revision  | 200      |
 +-------+--------+----------+--------+-----------+----------+

I tried doing it using a simple join. Here is the query:

SELECT a.Data,a.Acc_No, b.Type, b.Amount, bb.Type AS "Type_1", bb.Amount AS "Amount_1"
FROM TableA a,TableB b, TableB bb
WHERE a.Acc_No = b.Obj AND
      b.Obj = bb.Obj AND
      bb.Obj = a.Acc_No AND
      a.Acc_No =10;

But I got this output.

+------+--------+----------+--------+----------+----------+
| Data | Acc_No | Type     | Amount | Type_1   | Amount_1 |
+------+--------+----------+--------+----------+----------+
| Unix |     10 | rev      | 100.00 | rev      |   100.00 |
| Unix |     10 | revision | 200.00 | rev      |   100.00 |
| Unix |     10 | rev      | 100.00 | revision |   200.00 |
| Unix |     10 | revision | 200.00 | revision |   200.00 |
+------+--------+----------+--------+----------+----------+

I tried searching for the answer in this site and I even googled it but I didn't find the right answer to it.

Answer 1

Your current query is close but I'd suggest a few minor changes to it to get the result. If you want to "pivot" the data using JOINs, then you'll need to distinguish between which value you want to return from TableB in each subsequent join.

For example, when you want to return type=rev, you need to include a specific filter for that value. Then you'll do the same thing with type=revision. I'd also suggest using a LEFT JOIN to join to TableB in the event you don't have both type values for each Acc_no then you'll still return data.

select
  a.data,
  a.acc_no,
  b.type,
  b.amount,
  bb.type as type_1,
  bb.amount as amount_1
from tablea a
left join tableb b
  on a.acc_no = b.obj
  and b.type = 'rev'
left join tableb bb
  on a.acc_no = bb.obj
  and bb.type = 'revision';

See SQL Fiddle with Demo

You could also get this result using some conditional aggregation, then you don't have to join to TableB multiple times:

select
  a.data,
  a.acc_no,
  max(case when b.type = 'rev' then b.type end) as type,
  max(case when b.type = 'rev' then b.Amount end) as Amount,
  max(case when b.type = 'revision' then b.type end) as type_1,
  max(case when b.type = 'revision' then b.Amount end) as Amount_1
from tablea a
left join tableb b
  on a.acc_no = b.obj
group by a.data, a.acc_no
order by a.acc_no;

See SQL Fiddle with Demo