Otis Ottington Otis Ottington - 2 months ago 7
SQL Question

How rotate sql result entries into columns (pivot)

I have table a

| id | value | comment |
|--------------------------|
| 1 | Some1 | comm1 |
|--------------------------|
| 2 | Some2 | comm2 |
|--------------------------|


and i have table b with table a as foreign key

| id | id_a |name | amount | factor |
|--------------------------------------------|
| 1 | 1 |Car | 12 | 2 |
|--------------------------------------------|
| 2 | 1 |Bike | 22 | 5 |
|--------------------------------------------|
| 3 | 2 |Car | 54 | 1 |
|--------------------------------------------|
| 4 | 2 |Bike | 55 | 4 |
|--------------------------------------------|


As result I want to have a combination:

|id| value | comment | Car_Amount | Car_factor | Bike_Amount | Bike_Factor |
|--------------------------------------------------------------------------|
| 1| Some1 | comm1 | 12 | 2 | 22 | 5 |
|--------------------------------------------------------------------------|
| 2| Some2 | comm2 | 54 | 1 | 55 | 4 |
|--------------------------------------------------------------------------|


It is not a pivot as far as I can see. But I am not sure if this is good practise at all. I am not an expert in SQL things, but it looks utterly wrong to mix tables like that.
I mean "they" want to have it as a flat result to use it for reporting...

Is it possible at all?

thanks

Answer

Aggregate values like this:

select 
      a.id, a.value, a.comment,
      sum(case when b.name='Car'  then b.amount end) as Car_Amount,
      sum(case when b.name='Car'  then b.factor end) as Car_Factor,
      sum(case when b.name='Bike' then b.amount end) as Bike_Amount,
      sum(case when b.name='Bike' then b.factor end) as Bike_Factor
from a left join b on a.id=b.id_a
group by  a.id, a.value, a.comment;