So I've been reading through some of the other questions about dynamic rows to columns. By adapting another query in another answer here
mysql select dynamic row values as column names, another column as value,
I can get it to work for a single table, however I need to also pull records from two other tables to include in the result. Given these tables:
Cars
ID BRAND NAME etc1...
1 gmc sierra
2 ford ranger
3 dodge dakota
4 kia rio
Dice
ID DESCRIPTION
1 blue
2 green
3 red
etc2. etc2.
Stock
ID CAR_ID DICE_ID NUMBER
1 1 3 01V,3Y6
2 3 1 8Z4
3 2 2 03X
4 1 1 C7B
So yes this doesn't make much sense but it's only to show the structure. My result needs to come out looking like this:
CAR_ID BRAND NAME etc1. BLUE GREEN RED etc2.
1 gmc sierra ... C7B null 01V,3Y6 ...
2 ford ranger ... null 03X null ...
3 dodge dakota ... 8Z4 null null ...
4 kia rio ... null null null ...
Simple if it was static but the number of rows in Cars and Dice will be dynamic so it can't be hard-coded. I can get Dice to output the rows into columns but there are two things I can't figure out:
Does anyone know how to produce the desired results?
Edit 1: I forgot to say that the results must list ALL Cars whether or not they have Dice in stock.
Edit 2: I should have clarified that NUMBER meant product number and not count. This is a string field that can hold multiple product numbers. It's legacy data.
Try this:
SET SESSION group_concat_max_len = 10000;
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(D.ID = ',D.ID,', D.ID, NULL)) as \'', D.DESCRIPTION, '\'')) INTO @sql FROM Dice D;
SET @s = CONCAT('SELECT C.*, ', @sql, ' FROM Cars C INNER JOIN Stock S ON C.Id = S.Car_Id INNER JOIN Dice D ON S.DICE_ID = D.ID GROUP BY C.ID');
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments