Convert Duplicated Rows to Columns

Question

I have tried different approaches, I hope someone can help me with an elegant solution.

• I only have read access to the tables (can't create procedures) I have a Dashboard that accepts an MYSQL nested query.

I need to display a table: issues with the case numbers the Problem is that there are Issues that have more that one Case numbers

I have a

• Table Issues and
• Table Cases

I plan to join a derived table (were up to 4 CaseNr are displayed in 4 Columns) with the Issues Table. I need help deriving this table

Table: Cases

IssueIDfk | CaseNR | CaseStatus
-------------------------------
1         | A1     | Open
1         | A5     | Open
1         | C7     | Closed
2         | BA     | Open
2         | ZA     | Open
2         | 12     | Open
3         | A3     | Open
4         | 99     | Open

I need to join this to the Issues table IssueIDfk Without creating duplicate rows

Thus I need a derived table like this:

IssueIDfk | First_CaseNR | First_CaseStatus | Secnd_CaseNR | Secnd_CaseStatus..
-------------------------------
1         | A1           | Open             | A5           | Open ..
2         | BA           | Open             | ZA           | Open ..
3         | A3           | Open             | <NULL>       | <NULL> ..

then Max number of CaseNR per record is 4

My current query:

    SELECT DISTINCT
      Cases.IssueIDfk,
      (SELECT          t1.CaseNR     FROM      Case t1    WHERE      t1.IssueIDfk = Cases.IssueIDfk    LIMIT 1) AS First_CaseNR,
      (SELECT      Max(t2.CaseNR)    FROM      Case t2    WHERE      t2.IssueIDfk = Cases.IssueIDfk    LIMIT 2) AS Secnd_CaseNR,
      (SELECT      Max(t3.CaseNR)    FROM      Case t3    WHERE      t3.IssueIDfk = Cases.IssueIDfk    LIMIT 3) AS Thrd_CaseNR
      ...First_CaseStatus,
      ...Secnd_CaseStatus,
      ...Thrd_CaseStatus   ????

    FROM
      Cases

This SQL does not give nulls and I have not Idea how to add the CaseStatus columns

Tot create the Case Table use this:

r2d2 as Database

CREATE TABLE `r2d2`.`Case`
(
   `IssueIDfk_` INTEGER NOT NULL,
   `_CaseNR_` VARCHAR(12) NOT NULL,
   `_CaseStatus` VARCHAR(12) NULL,
   PRIMARY KEY (`_CaseNR_`)
);
INSERT INTO `r2d2`.`Case`
(`IssueIDfk_`,`_CaseNR_`,`_CaseStatus`)
VALUES
(1,' A1     ',' Open'),
(1,' A5     ',' Open'),
(1,' C7     ',' Closed'),
(2,' BA     ',' Open'),
(2,' ZA     ',' Open'),
(2,'12',' Open'),
(3,' A3     ',' Open'),
(4,'99',' Open');

UPDATE:.......Solution, works in my simulation but sadly, not in the dashboard

I modified the query from
https://stackoverflow.com/a/28067142/1435526 by Gordon Linoff

SELECT o.IssueID,
       MAX(case when rank = 1 then oc.CaseNumber end) as Case_1,
       MAX(case when rank = 1 then oc.Type end) as Type_1,
       MAX(case when rank = 1 then oc.CreatedDate end) as CreatedDate_1,
       MAX(case when rank = 2 then oc.CaseNumber end) as Case_2,
       MAX(case when rank = 2 then oc.Type end) as Type_2,
       MAX(case when rank = 2 then oc.CreatedDate end) as CreatedDate_2
FROM Issues o LEFT JOIN
     (SELECT 
        oc.*,
       (@rn := if(@o = IssueIDfk, @rn + 1, if(@o := IssueIDfk, 1, 1))) as rank

      FROM Cases oc CROSS JOIN
           (SELECT @rn := 0, @o := 0) vars
      ORDER BY oc.IssueIDfk
     ) oc
     ON o.IssueID = oc.IssueIDfk
GROUP BY o.IssueIDfk

Works in my simulation but sadly, not in the dashboard

ERROR 1351: View's SELECT contains a variable or parameter

UPDATE 2:

Working on https://stackoverflow.com/a/9652359/1435526 seems to work in simulation