List Database Result by Rows and Columns

Asked

Viewed 39 times

0

I have the following problem, I need to create a report with data coming from the database, listed by rows and columns, in the format below:

+----------+-----------+-----------+-----------+
|          |  01/2020  |  02/2020  |  03/2020  |
+----------+-----------+-----------+-----------+
|  USER_1  |    30%    |    75%    |    63%    |
+----------+-----------+-----------+-----------+
|  USER_2  |    30%    |    50%    |    25%    |
+----------+-----------+-----------+-----------+
|  USER_3  |    30%    |    75%    |    63%    |
+----------+-----------+-----------+-----------+

I already have a while returning from the bank the columns periodo, usuario e percentual

$sql = "SELECT periodo,percentual,usuario FROM a1";
$regis = ifx_query($sql,$con);
while($dados = ifx_fetch_row($regis)){
   var_dump($dados);
}

The return of the lines is simple, but the columns I do not know how to do and can not think of something.

php sql informix

  • By chance your "columns" are the contents of the field "period" recovered from your table? All users always have data for all periods?

  • Yes for both questions. The vertical column is the period field and the first column rows are the users and the core are the percentages.

  • I don’t know if Informix implements pivot Tables, if implementing is the most general solution. If you don’t implement a possible solution it’s a gambit in PHP.

  • They say you do, but there’s nothing clear on the Internet, not even with the people I consulted. One issue is that periods are dynamic and can change according to user selection.

1 answer

0


In the end I arrived at the following solution, starting from the suggestion of the above comments and looking for a solution of pivot Tables:

SELECT 
    user,
    max(case when periodo = '92020' then percentual else null end) as mes_9,
    max(case when periodo = '82020' then percentual else null end) as mes_8
FROM a4 group by 1

Browser other questions tagged php sql informix

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