How to create a view in MySQL to concatenate rows from a second table in CSV colmuns in the first place
I need to create a VIEW in MySQL that can concatenate two tables in such a way that for each row from the first table columns from the second table must be added, but as the row data must be formatted as multiple filds with multiple CSVs for each separate row.
My initial approach is with MySQL VIEW, but I was unable to find a way to show multiple row data from a second table as data from a single CSV cell in a view table.
Here are some examples:
1st table: gears
+------------+------------------------+-----------------+
| MainGearID | MainGearName | MainGearType |
+------------+------------------------+-----------------+
| 1 | Main Drive | Spur |
| 2 | Secondary Drive | Spur |
| 3 | Backup Secondary Drive | Hypoid |
| 4 | AUX Drive | Rack and pinion |
+------------+------------------------+-----------------+
2nd table: orbitinggears:
+----------+------------+--------------+--------------+
| OrbitaID | MainGearID | OrbitalType | OrbitalValue |
+----------+------------+--------------+--------------+
| 1 | 1 | Spur | 112 |
| 2 | 1 | Spur | 280 |
| 3 | 2 | Spur | 144 |
| 4 | 2 | Spur | 248 |
| 5 | 3 | Helical | 18 |
| 6 | 4 | Spur | 144 |
+----------+------------+--------------+--------------+
Required View:
+------------+------------------------+-----------------+----------+---------+
| MainGearID | MainGearName | MainGearType | Spur | Helical |
+------------+------------------------+-----------------+----------+---------+
| 1 | Main Drive | Spur | 112,280, | |
| 2 | Secondary Drive | Spur | 144,248, | |
| 3 | Backup Secondary Drive | Hypoid | | 18, |
| 4 | AUX Drive | Rack and pinion | 144, | |
+------------+------------------------+-----------------+----------+---------+
Does anyone have any ideas how to create a view this way?
+3
source to share
1 answer
You can use GROUP_CONCAT
with IF()
:
CREATE VIEW v6 AS
SELECT a.MainGearID, a.MainGearName, a.MainGearType,
GROUP_CONCAT(IF(b.OrbitalType='Spur',b.OrbitalValue,null)) AS Spur,
GROUP_CONCAT(IF(b.OrbitalType='Helical',b.OrbitalValue,null)) AS Helical
FROM gears a
JOIN orbitinggears b on b.MainGearID=a.MainGearID
GROUP BY a.MainGearID;
+4
source to share