0

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:

  • The main query is supposed to list all columns from the Cars table along with all rows as columns from the Dice table but I can't figure out how to attach the Dice query to the Cars query.
  • I need to pull the relative description from the Dice table to use as a column header. Using dice.id is not practical or helpful. I can't figure out how to incorporate this into my working query for the Dice table.

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.

Community
  • 1
  • 1
Chris
  • 5
  • 6

1 Answers1

0

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;
Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83
  • I'm getting an error: #1243 - Unknown prepared statement handler (stmt) given to EXECUTE – Chris Sep 07 '13 at 13:04
  • Removing the DEALLOCATE command fixed it. See my edit 1 and edit 2 for clarification. Also, thanks for your help. – Chris Sep 07 '13 at 13:28