is it somehow possible in mysql to get rows from a second table (content) to be displayed as columns in the first one (inventory)?
As the ID in the second table is not unique a JOIN produces duplicates, but i need all elements of the first table as a single row.
these are the tables: http://sqlfiddle.com/#!9/6da06/1
a result like this is needed:
id name location desc1 level1 desc2 level2 desc3 level3
1 test somewhere abc 20 def 50 ghi 30
2 anything something rfg 20 lzb 80 null null
3 xxyzyzy dffsdfd atc 20 null null null null
changes to the table structures are not possible, so is there any way how mysql can do that. there is no limit on how much duplicates of id can occur in table content... every row of inventory is needed, no duplicates. would be very cool, if sql can do something, or merging these arrays in PHP ?
If there is a limited number of content rows associated with each inventory row, then you can use the following query:
SELECT i.`id`, i.`name`, i.`location`,
MAX(CASE WHEN c.rn = 1 THEN c.`desc` END) AS desc1,
MAX(CASE WHEN c.rn = 1 THEN c.`level` END) AS level1,
MAX(CASE WHEN c.rn = 2 THEN c.`desc` END) AS desc2,
MAX(CASE WHEN c.rn = 2 THEN c.`level` END) AS level2,
MAX(CASE WHEN c.rn = 3 THEN c.`desc` END) AS desc3,
MAX(CASE WHEN c.rn = 3 THEN c.`level` END) AS level3
FROM inventory AS i
LEFT JOIN (
SELECT `id`, `desc`, `level`,
@row_number := IF (@id <> id,
IF (@id := id, 1, 1),
IF (@id := id, @row_number+1, @row_number+1)) AS rn
FROM content
CROSS JOIN (SELECT @row_number := 0, @id := 0) AS vars
ORDER BY `id`, `desc`
) AS c ON i.`id` = c.`id`
GROUP BY i.id, i.name, i.location
The above uses variables to assign incremental numbers to rows of content that belong to the same id slice. Conditional aggregation is then used to pivot correlated content rows.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments