Oracle PL/SQL 中的交叉表/透视查询 - iBatis - Extjs 和 JasperReport

发布于 2024-07-28 23:55:33 字数 947 浏览 4 评论 0原文

我尝试创建一个从 Oracle 10g 中的表创建的数据透视表。

这是表结构：

CREATE TABLE KOMUNIKA.STOCK_AREA
(
  PRODUCT_CODE  VARCHAR2(20 BYTE)               NOT NULL,
  PRODUCT_NAME  VARCHAR2(50 BYTE),
  AREA_CODE     VARCHAR2(20 BYTE),
  AREA_NAME     VARCHAR2(50 BYTE),
  QUANTITY      NUMBER(20,2)
)

我需要将这些数据显示为：

Name     US      Europe  Asia    SthAm   Aust    Africa Rest     Total 

C 2601   156     86      437     27      279     22      708     1,715 

C 2605   926     704     7,508   1,947   982     782     1,704   14,553 

Total    56,941  72,891  118,574 55,868  46,758  19,813  60,246  431,091

然后我将使用 iBatis 框架获取结果，然后将其显示在 ExtJs 网格中，如果有人遇到与我相同的问题并且想要的话，这对我来说真的是一个很大的帮助分享它。

我也已经找到一些资源来启动：

http://www.sqlsnippets.com/en /topic-12200.html

但如果你们中的任何人已经找到了更简单的解决方案，您将节省我的周末:(，

谢谢大家

原文

I tried to create a pivot table created from a table in Oracle 10g.

here is the table structure:

CREATE TABLE KOMUNIKA.STOCK_AREA
(
  PRODUCT_CODE  VARCHAR2(20 BYTE)               NOT NULL,
  PRODUCT_NAME  VARCHAR2(50 BYTE),
  AREA_CODE     VARCHAR2(20 BYTE),
  AREA_NAME     VARCHAR2(50 BYTE),
  QUANTITY      NUMBER(20,2)
)

and i need those data displayed as :

Name     US      Europe  Asia    SthAm   Aust    Africa Rest     Total 

C 2601   156     86      437     27      279     22      708     1,715 

C 2605   926     704     7,508   1,947   982     782     1,704   14,553 

Total    56,941  72,891  118,574 55,868  46,758  19,813  60,246  431,091

then i will grab the result using iBatis framework, then display it in a ExtJs Grid, it is really big favour from me, if anyone have same problem as me and want to share it.

i also already find some resource to start:

http://www.sqlsnippets.com/en/topic-12200.html

but if any of you have already find a simpler solution, you will save my weekend :(,

thank you all

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

颜 2024-08-04 23:55:33

您可以使用 CASE 表达式和 GROUP BY 在 SQL 本身中进行数据透视，只要结果中所需的列数是固定的（您不能编写返回可变列数的 sql。

假设您的区域如下所示：

AREA_CODE AREA_NAME
--------- ---------
      101 US
      102 Europe
      103 Asia
      104 South America
      105 Australia
      106 Africa
      107 ...
      108 ...

您可以编写一个查询来返回上面的结果：

  SELECT PRODUCT_NAME
       , SUM(CASE WHEN AREA_CODE = 101
                  THEN QUANTITY ELSE 0 END) US
       , SUM(CASE WHEN AREA_CODE = 102
                  THEN QUANTITY ELSE 0 END) Europe
       , SUM(CASE WHEN AREA_CODE = 103
                  THEN QUANTITY ELSE 0 END) Asia
       , SUM(CASE WHEN AREA_CODE = 104
                  THEN QUANTITY ELSE 0 END) SthAm
       , SUM(CASE WHEN AREA_CODE = 105
                  THEN QUANTITY ELSE 0 END) Aust
       , SUM(CASE WHEN AREA_CODE = 106
                  THEN QUANTITY ELSE 0 END) Africa
       , SUM(CASE WHEN AREA_CODE NOT IN (101, 102, 103, 104, 105, 106)
                  THEN QUANTITY ELSE 0 END) Rest
       , SUM(QUANTITY) Total
    FROM KOMUNIKA.STOCK_AREA
GROUP BY PRODUCT_NAME;

You can do the pivot in SQL itself, using CASE expressions and GROUP BY, as long as the number of columns you want in the result is fixed (you can't write sql that would return a variable number of columns.

Let's say your areas look like this:

AREA_CODE AREA_NAME
--------- ---------
      101 US
      102 Europe
      103 Asia
      104 South America
      105 Australia
      106 Africa
      107 ...
      108 ...

You can write a query that return the results you have above as:

  SELECT PRODUCT_NAME
       , SUM(CASE WHEN AREA_CODE = 101
                  THEN QUANTITY ELSE 0 END) US
       , SUM(CASE WHEN AREA_CODE = 102
                  THEN QUANTITY ELSE 0 END) Europe
       , SUM(CASE WHEN AREA_CODE = 103
                  THEN QUANTITY ELSE 0 END) Asia
       , SUM(CASE WHEN AREA_CODE = 104
                  THEN QUANTITY ELSE 0 END) SthAm
       , SUM(CASE WHEN AREA_CODE = 105
                  THEN QUANTITY ELSE 0 END) Aust
       , SUM(CASE WHEN AREA_CODE = 106
                  THEN QUANTITY ELSE 0 END) Africa
       , SUM(CASE WHEN AREA_CODE NOT IN (101, 102, 103, 104, 105, 106)
                  THEN QUANTITY ELSE 0 END) Rest
       , SUM(QUANTITY) Total
    FROM KOMUNIKA.STOCK_AREA
GROUP BY PRODUCT_NAME;

回复收藏 0 原文

~没有更多了~