在 Oracle 中使用数据透视表的建议

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/365238/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-18 17:33:57  来源:igfitidea点击:

Advice Using Pivot Table in Oracle

sqloracleplsqlpivot

提问by Tony Andrews

i need a report and i should use pivot table for it.Report will be group by categories .It is not good to use case when statement because there are many categories.u can think Northwind Database as sample and All Categories will be shown as Columns and Report will show customers preference among Categories.I dont know another solution and saw examples as Stored Procedures in Internet for Sql Server.Do u know a Solution except for using case when?

我需要一个报告,我应该使用数据透视表。报告将按类别分组。使用 case when 语句不好,因为有很多类别。您可以将 Northwind 数据库视为示例,所有类别将显示为列和报告将显示客户在类别中的偏好。我不知道另一种解决方案,并在 Internet 中将示例视为 Sql Server 的存储过程。你知道除了用例之外的解决方案吗?

Thanks

谢谢

回答by Tony Andrews

Once you get Oracle 11G there is a built-in PIVOT feature. Prior to that, you are restricted to using CASE (or DECODE) expressions. I have an article on how to automate doing that on my blog.

一旦您获得 Oracle 11G,就会有一个内置的 PIVOT 特性。在此之前,您只能使用 CASE(或 DECODE)表达式。我的博客上有一篇关于如何自动执行此操作的文章。

回答by derobert

It is painful to do row/column swaps in SQL. Each row you want to turn into a column, you have to ask for explicitly. So if you have many categories, your query will be very long, and it'll change every time you add/remove/change a category (this is probably the CASE method you're mentioning). You can write a stored procedure to generate that will generate that query for you (or otherwise build the result set you want), but honestly, in that way lies madness (and probably abysmal performance).

在 SQL 中进行行/列交换是很痛苦的。你想把每一行变成一列,你必须明确要求。因此,如果您有很多类别,您的查询将非常长,并且每次添加/删除/更改类别时它都会更改(这可能是您提到的 CASE 方法)。您可以编写一个存储过程来生成,该过程将为您生成该查询(或以其他方式构建您想要的结果集),但老实说,这种方式是疯狂的(并且可能是糟糕的性能)。

Take the data as rows, like SQL surely wants to give it, and then have your app convert it to columns. That'll be much easier than forcing SQL to do things it doesn't want to do.

将数据作为行,就像 SQL 肯定想要给它一样,然后让您的应用程序将其转换为列。这比强迫 SQL 做它不想做的事情容易得多。

回答by Alohci

You can use Oracle Data Cartridges (http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14289/dciwhatis.htm) to produce pivot tables more efficiently than case/when but be warned that it's not easy stuff, and I've found the documentation rather sparse in places.

您可以使用 Oracle Data Cartridges ( http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14289/dciwhatis.htm) 比 case/when 更有效地生成数据透视表,但请注意这并不容易东西,我发现文档在某些地方相当稀疏。

回答by jbaliuka

I use reporting tools to produce reports, it helps to keep views and queries clean and without formatting. Usually users ask for Excel and Excel is a very good reporting tool by itself. Excel has a lot ways to import data, but I suggest XML as input because you can open it as a regular Excel table.

我使用报告工具来生成报告,它有助于保持视图和查询干净且没有格式。通常用户会要求 Excel,而 Excel 本身就是一个非常好的报告工具。Excel 有很多导入数据的方法,但我建议将 XML 作为输入,因为您可以将其作为常规 Excel 表格打开。

  1. Use Excel to Open XML file with sample data
  2. Select Table and Click "Insert Pivot"
  3. Add formatting and save this file as template
  1. 使用 Excel 打开带有示例数据的 XML 文件
  2. 选择表格并点击“插入轴心”
  3. 添加格式并将此文件另存为模板

Excel remembers path to data XML file and you just have to replace data to refresh report. There are many other ways, I use JAVA application on web server to merge Excel template and SQL output. This code is free and open source but it won't help for non JAVA developers https://github.com/jbaliuka/x4j-analyticThere are many other reporting tools to produce "professional" reports.

Excel 会记住数据 XML 文件的路径,您只需替换数据即可刷新报表。还有很多其他方法,我在Web服务器上使用JAVA应用程序来合并Excel模板和SQL输出。此代码是免费和开源的,但对非 JAVA 开发人员没有帮助https://github.com/jbaliuka/x4j-analytic还有许多其他报告工具可以生成“专业”报告。