0

This query with left join syntax, relates two tables, the table tbl_t and the table tbl_p

SELECT
    tProgr,
    tLevel,
    pProg_related,
    pLevel 
FROM
    tbl_t t
    LEFT JOIN tbl_p p ON t.tProgr = p.tProgr 
WHERE
    t.tProgr = '2022-0071' 
ORDER BY
    t.tProgr DESC;

The return

-------------------------------------------------------
| tProgr    | tLevel     | pProg_related | pLevel     |
-------------------------------------------------------
| 2022-0071 | Principal  | 2022-0010     | Secondary  |
| 2022-0071 | Principal  | 2022-0065     | Secondary  |
| 2022-0071 | Principal  | 2022-0076     | Secondary  |
| 2022-0071 | Principal  | 2022-0182     | Secondary  |
| 2022-0071 | Principal  | 2022-0223     | Secondary  |
-------------------------------------------------------

How do I get this return instead?

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| tProgr    | tLevel     | pProg_related | pLevel     | pProg_related | pLevel     | pProg_related | pLevel     | pProg_related | pLevel     | pProg_related | pLevel     |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 2022-0071 | Principal  | 2022-0010     | Secondary  | 2022-0065     | Secondary  | 2022-0076     | Secondary  | 2022-0182     | Secondary  | 2022-0223     | Secondary  |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Any help really appreciated.


---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| tProgr    | tLevel     | pProg_related | pLevel     | pProg_related | pLevel     | pProg_related | pLevel     | pProg_related | pLevel     | pProg_related | pLevel     |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 2022-0071 | Principal  | 2022-0010     | Secondary  | 2022-0065     | Secondary  | 2022-0076     | Secondary  | 2022-0182     | Secondary  | 2022-0223     | Secondary  |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
George A. Custer
  • 119
  • 7
  • There are many solutions posted, for example https://stackoverflow.com/questions/25594902/row-to-column-transformation-in-mysql/25594969#25594969 or https://stackoverflow.com/questions/43721650/mysql-dynamic-rows-to-columns/43721751#43721751 or – Bill Karwin Mar 08 '23 at 16:13
  • See also http://mysql.rjweb.org/doc.php/pivot – Rick James Mar 29 '23 at 03:55

1 Answers1

1

To achieve the desired output where the related records are shown in separate columns, you can use the GROUP_CONCAT function along with the GROUP BY clause. Here is an example query that should give the desired output:

SELECT 
    tProgr, 
    tLevel, 
    GROUP_CONCAT(pProg_related SEPARATOR ', ') AS pProg_related, 
    GROUP_CONCAT(pLevel SEPARATOR ', ') AS pLevel
FROM 
    tbl_t t
    LEFT JOIN tbl_p p ON t.tID = p.tID 
WHERE 
    t.tProgr = '2022-0071' 
GROUP BY 
    t.tProgr, 
    t.tLevel 
ORDER BY 
    t.tProgr DESC;

In this query, we are using the GROUP_CONCAT function to concatenate the related records separated by a comma for each group of tProgr and tLevel. The GROUP BY clause is used to group the results by the tProgr and tLevel columns.

Meet
  • 42
  • 3