MYSQL dynamic query from rows to columns

Question

I have a table

or_id	emp_id	cs	val
100	1	x	3.4
100	1	x	4.5
100	1	y	5
100	1	y	6
200	2	a	12
200	2	b	11
200	2	c	14

I want my output table like:

or_id	emp_id	CS1	CS2	CS3
100	1	x	y
200	2	a	b	c

I tried every possible code but nothing seems to work. I want dynamic code for this.

This query is working, but for larger dataset the execution time is lengthy, so I need an optimized code.

select distinct or_id,emp_id,
(select cs from (
select distinct cost_center from orl where emp_id=m.emp_id) a limit 1 offset 0 ) cs1,
(select cost_center from (
select distinct cost_center from orl  where emp_id=m.emp_id) a limit 1 offset 1 ) cs2,
(select cost_center from (
select distinct cost_center from orl where emp_id=m.emp_id) a limit 1 offset 2 ) cs3
from orl m

Please be clear about what you want to achieve, and what code have you used? — charlesdk, Oct 07 '22 at 08:36
Confusing and irrelevant tags removed! Add back only the relevant tags please. Tags are designed to gather the correct audience for your question, adding rubbish tags does not help — RiggsFolly, Oct 07 '22 at 08:42
Does this answer your question? [How can I return pivot table output in MySQL?](https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql) — RiggsFolly, Oct 07 '22 at 08:58
Each make and version of database server has its own dialect of SQL. Please [edit] your question to add a [tag](//stackoverflow.com/help/tagging) for the database you use. [tag:postgresql]? [tag:mysql]? [tag:sql-server]? [tag:oracle]? [tag:google-bigquery]? [tag:amazon-redshift]? Another? — O. Jones, Oct 07 '22 at 09:25
Is there any specific reason for the ordering of "a, b, c" in the second line instead of "b, c, a" or other ordering? — The Impaler, Oct 07 '22 at 13:20
no there isn't any specific reason for the ordering it can be random — educational board, Oct 08 '22 at 09:21
Will you always return three CS columns? (SQL is statically and strongly typed, in part that means result sets always have the same columns. Wanting a dynamic number of columns is often a sign of a flawed design / code smell. If that's what you want, please also describe why as there are often more suitable design patterns in SQL). — MatBailie, Oct 08 '22 at 09:24
Please specify which version of MySQL, as MySQL8 has many features not available in MySQL5.x — MatBailie, Oct 08 '22 at 09:25
The idea here is that for the same emp_id whenever I get a new unique row that will contain a unique value ( in column CS), that should be converted to a new column [cs1,cs2...] in the resultant table. Let say I got a new value 'PQR' in (column CS) for emp_id =2 then in resultant table I want a new column name CS4 which will contain this value 'PQR'. Currently I m working on Mysql 8.0. — educational board, Oct 08 '22 at 09:35
`If that's what you want, please also describe *why* as there are often more suitable design patterns in SQL.` *(SQL quite deliberately doesn't natively support dynamically varying numbers of columns. Wanting that indicates you're trying to use SQL against its intentional design. This can either indicate that a different pattern would be better suited, or that formatting the data in a different layer / language would be more appropriate. Unless you explain the reasons for your desired behaviour, this appears as an https://xyproblem.info problem.)* — MatBailie, Oct 08 '22 at 09:39
according to the data I have , yes It will always return three CS_ columns and for that I have used a subquery but it was not efficient as the query was heavy and taking longer time to execute — educational board, Oct 08 '22 at 09:40
Please do not use `distinct` when it is not needed. It is not needed because you also use `LIMIT 1`. — Luuk, Oct 08 '22 at 10:06
Use `GROUP_CONCAT(DISTINCT cs)` instead of 3 columns for the cs values — Rick James, Oct 11 '22 at 01:06

MatBailie · Accepted Answer · 2022-10-08T09:57:59.510

For three CS columns, in MYSQL8

WITH
  sorted AS
(
  SELECT
    or_id,
    emp_id,
    cs,
    ROW_NUMBER() OVER (PARTITION BY or_id, emp_id ORDER BY cs) AS ordinal
  FROM
    your_table
  GROUP BY
    or_id,
    emp_id,
    cs
)
SELECT
  or_id,
  emp_id,
  MAX(CASE WHEN ordinal = 1 THEN cs END) AS cs1,
  MAX(CASE WHEN ordinal = 2 THEN cs END) AS cs2,
  MAX(CASE WHEN ordinal = 3 THEN cs END) AS cs3
FROM
  sorted
GROUP BY
  or_id,
  emp_id

Demo: https://dbfiddle.uk/2QNiXt6O

MYSQL dynamic query from rows to columns

1 Answers1