Mysql:在另一列下面显示列的值 [英] Mysql: Display values of column below another column

问题描述

我真的陷入了困境.我已经尝试通过准备可执行语句来实现联合.我非常清楚，SQL不是用于此类任务的语言，但是到目前为止，目标是创建一个原型.实际的技术实现将在以后发生.

I am really stuck in this. I have tried union, by preparing a an executable statement. I am very well aware that SQL is not a language meant for such type of tasks, but as of now, the objective is to create a prototype. The actual technical implementations would happen later.

我将解释我的要求.

有一个临时表，我在其中存储名称，项目和与该项目相关的功能.列将行存储为:

There is a temporary table, in which I am storing name, project and features related to that project. The columns store rows as:

样本数据:

   project, Tracker, resource_name, Total_Hours, date, project_counts, 

   'Slim', 'Feature', 'babu.balakrishnan', '39', '2016-10-22', '20'
   'Slim', 'Feature', 'prasenjit.ghosh',  '1.5', '2016-10-22', '2'
   'Slim', 'Feature', 'shamim.akhtar',    '3',   '2016-10-22', '2'
   'Slim', 'Support', 'babu.balakrishnan', NULL, '2016-10-22', '1'
   'Slim', 'Support', 'shamim.akhtar',     '3',  '2016-10-22', '2'

我希望以这种方式显示输出:

I want output to be displayed in this manner:

   babu.balakrishnan    Slim     2016-10-22    
                       Feature      20                        
                       Support      1             


      prasenjit.ghosh   Slim                                   
                        Feature     3              

我知道如何在一行中显示开发人员名称，项目名称和日期，但是我不知道如何继续在项目名称下方显示项目状态.此外，这仅适用于一位开发人员.我需要对与开发人员相关联的所有项目重复此操作，然后对其他开发人员也要重复此操作.

I know how to display name of developer, name of project and date in one row, but I am not able to figure out how shall I proceed to display the status of project below the project name. Also, this is for only one developer. I need to repeat this for all the projects, with which a developer is associated and then, also, for other developers too.

       SELECT
       GROUP_CONCAT(DISTINCT
       CONCAT(
       'CASE WHEN date = ''',
       date,
       ''' then project_counts end AS `', date, '`' )
       ORDER BY date ) INTO @sql
       FROM finance_dashboard.resource_utilization_data;

       SELECT
       GROUP_CONCAT(DISTINCT
       CONCAT(
       'CASE WHEN project = ''', 
       project,
       ''' then Tracker end AS `', ' ' , '`' )
       ORDER BY date ) INTO @sql2
       FROM finance_dashboard.resource_utilization_data;

       SET @sql = CONCAT('select distinct resource_name, project        
       ,',@sql, ' from finance_dashboard.resource_utilization_data 
       union
       select '''' ,', @sql2, '," " FROM        
       finance_dashboard.resource_utilization_data group by 
       Tracker');        

我得到的输出是:

       'babu.balakrishnan',  'Slim', '20'
       'prasenjit.ghosh',    'Slim', '2'
       'shamim.akhtar',      'Slim', '2'
       'babu.balakrishnan',  'Slim', '1'
                         '', 'Feature', ' '

我希望相应开发人员的功能，支持或该开发人员的任何状态显示在亭亭玉立的下方.

I want Feature, Support or whatever status for that developer, to appear below slim, for respective developers.

在查询级别上可以吗?我知道，必须使用聚合函数.

Is this possible at query level? I know, aggregate function has to be used.

还可以动态绑定变量，以便将特定行的值写入列下方吗?

Also, is it possible to bind variables dyamically, so that the value of particular row gets written below the column?

谢谢

推荐答案

$db=new mysqli("localhost","root","","test");

$result=$db->query("select date,project_id,name ... from tableName");

while(list($date,$project_id,$name)=$result->fetch_row()){
   echo "<div class='record'>";
      echo "<div class='field'><strong>Date:</strong> $date</div>";
      echo "<div class='field'><strong>Project:</strong> $project_id</div>";
      echo "<div class='field'><strong>Name:</strong> $name</div>";
   echo "</div>";

 }

这篇关于Mysql:在另一列下面显示列的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！