MYSQL dynamic query from rows to columns-CodePudding

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

CodePudding user response：

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