I am trying to transpose the data from the first table to the second.
original data (number of cars and states are limited):
+----+----------+-------+--------+
| id | car | state | tstamp |
+----+----------+-------+--------+
| 01 | toyota | new | 1900 |
| 02 | toyota | old | 1950 |
| 03 | toyota | scrap | 1980 |
| 04 | mercedes | new | 1990 |
| 05 | mercedes | old | 2010 |
| 06 | tesla | new | 2013 |
+-----+---------------+----------+
query result:
+----------+------+------+-------+
| car | new | old | scrap |
+----------+------+------+-------+
| toyota | 1900 | 1950 | 1980 |
| mercedes | 1990 | 2010 | null |
| tesla | 2013 | null | null |
+----------+------+------+-------+
My SQL Skills are somewhat rusty therefore I would appreciate any help!
Something like this would work, depending on how your data is organised:
SELECT
car,
MAX(CASE WHEN state = 'new' THEN tstamp END) AS new,
MAX(CASE WHEN state = 'old' THEN tstamp END) AS old,
MAX(CASE WHEN state = 'scrap' THEN tstamp END) AS scrap
FROM
table
GROUP BY
car;
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加