如何在Oracle中创建交叉表/数据透视表以在Web(PHP)中显示? [英] How to create crosstab/pivot table in oracle to show in web(PHP)?

问题描述

亲爱的所有人，

我有一张Oracle表，我想选择它并以php作为交叉表/数据透视表显示在Web中，结果如下:


餐桌产品

Dear All,

I have one table of oracle and i want select it and show in web by php as crosstab/pivot as table and result bellow:


Table Product

Date		Product		Values
---------------------------------------
06/14/2012      A		5
06/14/2012	B		3
06/14/2012	C		5
06/15/2012	A		3
06/15/2012	B		4
06/15/2012	C		3
06/15/2012	D		2
.		.		.
.		.		.

在网络中显示

Show in Web

Date		A	B	C	D	
------------------------------------------
06/14/2012	5	3	5	0
06/15/2012	3	4	3	2

谢谢，
Golden.

Thanks,
Golden.

推荐答案

您正在使用的数据库是什么?

对于SQL Server，我发现它们具有很好的PIVOT操作.请参阅 http://www.mssqltips.com/sqlservertip/1019/crosstab-queries-using-pivot-in-sql-server-2005/ [ http://www.simple-talk.com/sql/t-sql-programming/creating-cross-tab-queries-and-pivot-tables-in-sql/ [

What''s the database you''re using?

For SQL Server, I found that they have a nice PIVOT operation. See http://www.mssqltips.com/sqlservertip/1019/crosstab-queries-using-pivot-in-sql-server-2005/[^]

You can also use standard SQL CASE statement for this purpose too. See http://www.simple-talk.com/sql/t-sql-programming/creating-cross-tab-queries-and-pivot-tables-in-sql/[^]

SELECT date,
       SUM(a) a_total, 
       SUM(b) b_total, 
       SUM(c) c_total, 
       SUM(d) d_total
FROM (SELECT date, 
             CASE WHEN product = 'A' THEN values ELSE 0 END a,
             CASE WHEN product = 'B' THEN values ELSE 0 END b,
             CASE WHEN product = 'C' THEN values ELSE 0 END c,
             CASE WHEN product = 'D' THEN values ELSE 0 END d
      FROM   product)
group by date;

这篇关于如何在Oracle中创建交叉表/数据透视表以在Web(PHP)中显示?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！