List columns according to current date

Asked

Viewed 200 times

3

I have the following view:

select * from chamdosrow

It returns as follows:

NOME    1   2   3   4   5   6   7   8   9   10  11  12  13  14  15  16  17  18  19  20  21  22  23  24  25  26  27  28  29  30  31
----------------------------------------------------------------------------------------------------------------------------------- 
MARCOS  0   1   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
PAULO   0   5   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
JOAO    0   2   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
JOSE    0   1   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
VITOR   0   1   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
DANIEL  0   6   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
CAIO    0   2   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
MARCEL  0   4   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
PEDRO   0   2   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0

The columns:

1   2   3   4   5   6   7   8   9   10  11  12  13  14  15  16  17  18  19  20  21  22  23  24  25  26  27  28  29  30  31

It references the dates of the month, what I need is that only list the columns of the 1 day until the current day, how could do it ?

mysql query view
  • 3

    To select a column list dynamically you will need a dynamic query. Another solution could use pivot. An even better one would be to create a better data modeling

  • Will you run this via program? What language? Do you have any initial code snippets? So we can help you build the dynamic query as well suggested by @jean.

3 answers

3


The best would be to review the data model but, if you really want to proceed, here is an option:

SET @DiaHoje := DAY(CURDATE());

SELECT CONCAT('SELECT nome, ', GROUP_CONCAT(CONCAT('`', c.COLUMN_NAME, '`')), ' FROM chamdosrow;')
INTO @query
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = 'chamdosrow'
  AND CAST(c.COLUMN_NAME AS UNSIGNED) <= @DiaHoje
ORDER BY c.ORDINAL_POSITION;

PREPARE stmt FROM @query;

EXECUTE stmt;

It is quite simple, but if you wish, I can leave an explanation here. Here is also the usual Sqlfiddle

The example above works when you directly access a table, in your case, and because you are accessing a view, you can do it as follows, to get the columns name dynamically:

SET @DiaHoje := DAY(CURDATE());

SELECT CONCAT('SELECT ', GROUP_CONCAT(CONCAT('`', Dia, '`')), ' FROM chamdosrow;')
INTO @query
FROM (
  SELECT @row := @row + 1 AS Dia 
  FROM INFORMATION_SCHEMA.TABLES t1,
  (SELECT @row:=0) r 
) DiasMes       
WHERE Dia <= @DiaHoje
ORDER BY Dia;

PREPARE stmt FROM @query;
EXECUTE stmt;

Stay here the Second Sqlfiddle.

2

An approach with a somewhat less complex logic, manipulating a list with the name of the equally spaced fields:

SELECT CONCAT( 
   'SELECT nome, ',
    SUBSTRING(
         ' 1, 2, 3, 4, 5, 6, 7, 8, 9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31', 
         1, 
         (DAY(CURDATE())*3)-1
    ),
    ' FROM chamdosrow '
) INTO @query FROM dual;

PREPARE st FROM @query;
EXECUTE st;

2

A solution similar to Gustavo’s, but without having to list all fields (a detail, I changed the fields from 1 to 9, putting a zero in front):

SELECT
  CONCAT(
    'SELECT ',
    SUBSTRING_INDEX(
      GROUP_CONCAT(CONCAT('`', column_name, '`') ORDER BY column_name),
      ',',
      day(curdate())),
    ' FROM tabela'
  )
FROM
  information_schema.columns 
WHERE
  table_schema=DATABASE() 
  AND table_name='tabela'
INTO @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;

See on SQL Fiddle

Browser other questions tagged mysql query view

You are not signed in. Login or sign up in order to post.