CODEWITHSUNDEEP
mysqlselectdynamicpivot-tablerows
Ara
Ara

Reputation: 13

MySQL Pivoting Rows to Dynamic Columns

we have a table that include people entrance/exit records as follow :

Person Day Time
1 02/21/2021 08:10
2 02/21/2021 08:11
1 02/21/2021 08:45
1 02/21/2021 09:18
1 02/21/2021 10:45
7 02/21/2021 10:53
2 02/21/2021 17:06
1 02/21/2021 17:23
7 02/21/2021 17:31
1 02/22/2021 08:13
5 02/22/2021 08:19
2 02/22/2021 08:20
2 02/22/2021 08:23
5 02/22/2021 09:47
5 02/22/2021 11:03
5 02/22/2021 18:06
5 02/22/2021 19:08
2 02/22/2021 19:01
5 02/22/2021 22:37
5 02/23/2021 08:15
1 02/23/2021 08:15
1 02/23/2021 14:30
5 02/23/2021 17:05

with these data, we want to select them as follow :

Person Day Time1 Time2 Time3 Time4 Time5 Time6
1 02/21/2021 08:10 08:45 09:18 10:45 17:23
2 02/21/2021 08:11 17:06
7 02/21/2021 10:53 17:31
1 02/22/2021 08:13
2 02/22/2021 08:20 08:23 19:01
5 02/22/2021 08:19 09:47 11:03 18:06 19:08 22:37
1 02/23/2021 08:15 14:30
5 02/23/2021 08:15 17:05

Upvotes: 1

Views: 1081

Answers (2)

Rick James
Rick James

Reputation: 142208

For a data-driven list of column needs, see http://mysql.rjweb.org/doc.php/pivot

It builds the SELECT and optionally runs it.

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562250

The easiest solution is not to do this in SQL, but to just fetch all the data with a simple query:

SELECT person, day, time FROM WeHaveATable ORDER BY day, person, time;

Then write application code to present it in a grid however you want.

The reason that this is tricky in SQL is that SQL requires you spell out all the columns in the select-list before you prepare the query. That's before it gets a chance to read the data to know how many columns there would be for the person with the greatest number of times.

There is no way in SQL to generate "dynamic columns" by reading the data and appending more columns to the select-list based on what it discovers while reading data.

So the way to do a pivot in SQL is that you first must know how many columns.

SELECT MAX(c) FROM (SELECT COUNT(*) FROM WeHaveATable GROUP BY person) AS t;

Then form a query that numbers the rows per person/day using a window function, and use that in a pivot-table query, with one column for each time, up to the max number of times you got in the previous query.

WITH cte AS (
  SELECT person, day, time, ROW_NUMBER() OVER (PARTITION BY day, person ORDER BY time) AS colno
  FROM WeHaveATable;
)
SELECT day, person,
  MAX(CASE colno WHEN 1 THEN time END) AS Time1,
  MAX(CASE colno WHEN 2 THEN time END) AS Time2,
  MAX(CASE colno WHEN 3 THEN time END) AS Time3,
  MAX(CASE colno WHEN 4 THEN time END) AS Time4,
  MAX(CASE colno WHEN 5 THEN time END) AS Time5,
  MAX(CASE colno WHEN 6 THEN time END) AS Time6
FROM cte
GROUP BY day, person;

If this seems like a lot of confusing, meticulous work, you're right. That's why it's recommended to skip solving this in SQL. Do the simple query I showed at the top, then write application code to process the results into the grid like you want.

Upvotes: 2

Related Questions