MYSQL number of variable columns

Question

MYSQL number of variable columns

Navigation

#1 by (0 votes)

1

I'm trying to perform a query on mysql that shows me the following information.

I have a table that registers the hour of income workers, I would like to take a date filter, for example from 2018-10-15 to 2018-10-31, the relation of the hours of entry.

SELECT
r1.id_trab,
  MAX(CASE WHEN r.dia = '1'
    THEN r1.hor_ent ELSE 0 END) AS 'hor_ent_dia1',
  MAX(CASE WHEN r.dia = '2'
    THEN r1.hor_ent ELSE 0 END) AS 'hor_ent_dia2',
  MAX(CASE WHEN r.dia = '3'
    THEN r1.hor_ent ELSE 0 END) AS 'hor_ent_dia3',
  MAX(CASE WHEN r.dia = '4'
    THEN r1.hor_ent ELSE 0 END) AS 'hor_ent_dia4',
  MAX(CASE WHEN r.dia = '5'
    THEN r1.hor_ent ELSE 0 END) AS 'hor_ent_dia5',
  MAX(CASE WHEN r.dia = '6'
    THEN r1.hor_ent ELSE 0 END) AS 'hor_ent_dia6',
  MAX(CASE WHEN r.dia = '7'
    THEN r1.hor_ent ELSE 0 END) AS 'hor_ent_dia7',
  MAX(CASE WHEN r.dia = '8'
    THEN r1.hor_ent ELSE 0 END) AS 'hor_ent_dia8',
  MAX(CASE WHEN r.dia = '9'
    THEN r1.hor_ent ELSE 0 END) AS 'hor_ent_dia9',
  MAX(CASE WHEN r.dia = '10'
    THEN r1.hor_ent ELSE 0 END) AS 'hor_ent_dia10',
  MAX(CASE WHEN r.dia = '11'
    THEN r1.hor_ent ELSE 0 END) AS 'hor_ent_dia11',
  MAX(CASE WHEN r.dia = '12'
    THEN r1.hor_ent ELSE 0 END) AS 'hor_ent_dia12',
  MAX(CASE WHEN r.dia = '13'
    THEN r1.hor_ent ELSE 0 END) AS 'hor_ent_dia13',
  MAX(CASE WHEN r.dia = '13'
    THEN r1.hor_ent ELSE 0 END) AS 'hor_ent_dia12',
  MAX(CASE WHEN r.dia = '14'
    THEN r1.hor_ent ELSE 0 END) AS 'hor_ent_dia14',
  MAX(CASE WHEN r.dia = '15'
    THEN r1.hor_ent ELSE 0 END) AS 'hor_ent_dia15',
  MAX(CASE WHEN r.dia = '16'
    THEN r1.hor_ent ELSE 0 END) AS 'hor_ent_dia16',
  MAX(CASE WHEN r.dia = '17'
    THEN r1.hor_ent ELSE 0 END) AS 'hor_ent_dia17',
  MAX(CASE WHEN r.dia = '18'
    THEN r1.hor_ent ELSE 0 END) AS 'hor_ent_dia18',
  MAX(CASE WHEN r.dia = '19'
    THEN r1.hor_ent ELSE 0 END) AS 'hor_ent_dia19',
  MAX(CASE WHEN r.dia = '20'
    THEN r1.hor_ent ELSE 0 END) AS 'hor_ent_dia20',
  MAX(CASE WHEN r.dia = '21'
    THEN r1.hor_ent ELSE 0 END) AS 'hor_ent_dia21',
  MAX(CASE WHEN r.dia = '22'
    THEN r1.hor_ent ELSE 0 END) AS 'hor_ent_dia22',
  MAX(CASE WHEN r.dia = '23'
    THEN r1.hor_ent ELSE 0 END) AS 'hor_ent_dia23',
  MAX(CASE WHEN r.dia = '24'
    THEN r1.hor_ent ELSE 0 END) AS 'hor_ent_dia24',
  MAX(CASE WHEN r.dia = '25'
    THEN r1.hor_ent ELSE 0 END) AS 'hor_ent_dia25',
  MAX(CASE WHEN r.dia = '26'
    THEN r1.hor_ent ELSE 0 END) AS 'hor_ent_dia26',
  MAX(CASE WHEN r.dia = '27'
    THEN r1.hor_ent ELSE 0 END) AS 'hor_ent_dia27',
  MAX(CASE WHEN r.dia = '28'
    THEN r1.hor_ent ELSE 0 END) AS 'hor_ent_dia28',
  MAX(CASE WHEN r.dia = '29'
    THEN r1.hor_ent ELSE 0 END) AS 'hor_ent_dia29',
  MAX(CASE WHEN r.dia = '30'
    THEN r1.hor_ent ELSE 0 END) AS 'hor_ent_dia30',
  MAX(CASE WHEN r.dia = '31'
    THEN r1.hor_ent ELSE 0 END) AS 'hor_ent_dia31'
FROM reloj r1
LEFT JOIN
  (SELECT DAY(fecha) AS dia,
    MONTH(fecha) AS mes FROM reloj r WHERE r.fecha BETWEEN '2019-05-15'
    AND '2019-05-30'
    GROUP BY DAY(fecha)) AS r
ON DAY(r1.fecha) = r.dia
WHERE r1.fecha BETWEEN '2019-05-15'
AND '2019-05-30'
GROUP BY r1.id_trab;

The query so far shows me the information of the whole month, I would like to improve it giving you the option to choose the range of dates and only show me THE DAYS that are that that range and not all month , since now if it is not within the range, it shows me ALL DAYS with empty data. That if I choose a range of 10 days, show me only 10 COLUMNS, if I ask for a range of 27 days, I will show 27 columns and that way.

Since I appreciate all the help you can give me. I enclose the database and tables that help to carry out the exercise.

DATABASE

mysql

asked by Joel Medrano 19.10.2018 в 22:41

source

1 answer

Problem with access to MySql database How is a webservice with SSL certificates published in IIS and how can I later consume it in VS2015?

score 0 · Accepted Answer

This is not easy, but it can be done with a statement

Let's start with the basics. If you put

SELECT GROUP_CONCAT(
    CONCAT(
    'MAX(CASE WHEN DAY(fecha)=',
     dia,
     ' THEN hor_ent ',
     ' ELSE 0 END) ',
     ' AS hor_ent_dia',
     dia
    )
 )
FROM 
(SELECT DAY(fecha) AS dia FROM reloj 
 WHERE fecha BETWEEN '2019-05-15' AND '2019-05-30' GROUP BY DAY(fecha)
) r;

The result is a text that says:

MAX(CASE WHEN DAY(fecha)=15 THEN hor_ent  ELSE 0 END)  AS hor_ent_dia15,MAX(CASE WHEN DAY(fecha)=23 THEN hor_ent  ELSE 0 END)  AS hor_ent_dia23,MAX(CASE WHEN DAY(fecha)=24 THEN hor_ent  ELSE 0 END)  AS hor_ent_dia24,MAX(CASE WHEN DAY(fecha)=25 THEN hor_ent  ELSE 0 END)  AS hor_ent_dia25,MAX(CASE WHEN DAY(fecha)=26 THEN hor_ent  ELSE 0 END)  AS hor_ent_dia26

so that if you could create a query with that text in between, you would have solved the problem.

Now, it is not to arrive and put an arbitrary text as part of a query. For that there are the statements. Think you can do:

SET @campos = ' id_trab, fecha ';
SET @consulta = CONCAT('SELECT ',@campos,' FROM reloj');
PREPARE stmt FROM @consulta;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

And with that you executed a string as if it were a query.

Putting together the above with the above, we can put together the final query as:

SET @campos = NULL;
SET @consulta = NULL;

SELECT (@campos := GROUP_CONCAT(
    CONCAT(
    'MAX(CASE WHEN DAY(fecha)=',
     dia,
     ' THEN hor_ent ',
     ' ELSE 0 END) ',
     ' AS hor_ent_dia',
     dia
    )
 ))
FROM 
(SELECT DAY(fecha) AS dia FROM reloj 
 WHERE fecha BETWEEN '2019-05-15' AND '2019-05-30' GROUP BY DAY(fecha)
) r;
SET @consulta = CONCAT('SELECT  id_trab, ', @campos, ' 
                   FROM reloj
                   WHERE fecha BETWEEN ''2019-05-15'' AND ''2019-05-30'' GROUP BY id_trab');
PREPARE stmt FROM @consulta;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

And that would give you the final result showing only the columns that have data.