转置表 [英] Transposing Table

问题描述

我到处找，没有找到任何有用的东西.

I've looking everywhere and have not finding anything useful.

我有一张表格，用于为员工提供帮助.

I have a table the captures assistance for employees.

看起来像这样的表格:

ID   | DATE     | ATTENDANCE
________________
2524 | 20121001 | ASISTANCE
2525 | 20121001 | ABSCENCE
2526 | 20121001 | ASISTANCE
2527 | 20121001 | ASISTANCE
2524 | 20121002 | ASISTANCE
2525 | 20121002 | ABSCENCE
2526 | 20121002 | ASISTANCE
2527 | 20121002 | ASISTANCE
2524 | 20121003 | ASISTANCE
2525 | 20121003 | DAY OFF
2526 | 20121003 | DAY OFF
2527 | 20121003 | ASISTANCE

我想要一个查询，返回一个看起来像这样的表:

And I want a query that returns a table that will look like this:

ID   | 20121001  | 20121002  | 20121003
________________
2524 | ASISTANCE | ASISTANCE | ASISTANCE
2525 | ABSCENCE  | ABSCENCE  | DAY OFF
2526 | ASISTANCE | ASISTANCE | ASISTANCE
2527 | ASISTANCE | ASISTANCE | DAY OFF

我尝试了单独的查询并加入了它们，但由于它们有很多日期，所以这样做需要太多时间.

I tried individual querys and joining them, but since they are to many dates it takes too much to do so.

我怎样才能做到既高效又可以存储到视图或函数中?

How can I do it that is efficient and can be stored into a view or function??

推荐答案

使用像 PHP 这样的服务器端语言来获取和处理数据会更容易.那么构建数组将是一件微不足道的事情:

It would be easier to get the data and process it in a server-side language like PHP. It would then be a trivial matter to build the array:

$entry[$id][$date] = $status;

那么:

echo "ID";
foreach(array_keys(array_values($entry)[0]) as $date) {
    // requires some temporary variables in PHP before 5.4
    echo "\t".$date;
}
foreach($entry as $id=>$days) {
    echo "\n".$id;
    foreach($days as $day) echo "\t".$day;
}

您现在有一个制表符分隔的表格.

You now have a tab-separated table.

这篇关于转置表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！