Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.9k views
in Technique[技术] by (71.8m points)

database - 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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)
SELECT t1.id,
       t1.value,
       MAX(CASE WHEN t2.name = 'Car'  THEN t2.amount END) AS Car_Amount,
       MAX(CASE WHEN t2.name = 'Car'  THEN t2.factor END) AS Car_Factor,
       MAX(CASE WHEN t2.name = 'Bike' THEN t2.amount END) AS Bike_amount,
       MAX(CASE WHEN t2.name = 'Bike' THEN t2.factor END) AS Bike_Factor
FROM a t1
INNER JOIN b t2
    ON t1.id = t2.id_a
GROUP BY t1.id

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...