Oracle PIVOT，两次？

发布于 2024-09-08 05:49:09 字数 581 浏览 10 评论 0原文

我一直在尝试放弃在 Oracle 11g 中使用 DECODE 来透视行，其中有一个方便的 PIVOT 函数。但我可能发现了一个限制：

我试图为基表中的每个值返回 2 列。比如：

SELECT somethingId, splitId1, splitName1, splitId2, splitName2
FROM (SELECT somethingId, splitId
      FROM SOMETHING JOIN SPLIT ON ... )
      PIVOT ( MAX(splitId) FOR displayOrder IN (1 AS splitId1, 2 AS splitId2),
              MAX(splitName) FOR displayOrder IN (1 AS splitName1, 2 as splitName2)
            )

我可以用 DECODE 来做到这一点，但我无法通过语法来让我用 PIVOT 来做到这一点。这可能吗？看起来这个函数处理起来并不太难。

编辑：StackOverflow 可能不是 SQL 问题的正确溢出吗？

编辑：有人在吗？

原文

I have been trying to move away from using DECODE to pivot rows in Oracle 11g, where there is a handy PIVOT function. But I may have found a limitation:

I'm trying to return 2 columns for each value in the base table. Something like:

SELECT somethingId, splitId1, splitName1, splitId2, splitName2
FROM (SELECT somethingId, splitId
      FROM SOMETHING JOIN SPLIT ON ... )
      PIVOT ( MAX(splitId) FOR displayOrder IN (1 AS splitId1, 2 AS splitId2),
              MAX(splitName) FOR displayOrder IN (1 AS splitName1, 2 as splitName2)
            )

I can do this with DECODE, but I can't wrestle the syntax to let me do it with PIVOT. Is this even possible? Seems like it wouldn't be too hard for the function to handle.

Edit: is StackOverflow maybe not the right Overflow for SQL questions?

Edit: anyone out there?

分享到QQ

分享到微博

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

发布评论

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

一张白纸 2024-09-15 05:49:09

从 oracle-developer.net 看来，它可以像这样完成这：

SELECT somethingId, splitId1, splitName1, splitId2, splitName2
FROM (SELECT somethingId, splitId
      FROM SOMETHING JOIN SPLIT ON ... )
      PIVOT ( MAX(splitId)  ,
              MAX(splitName) 
              FOR displayOrder IN (1 AS splitName1, 2 as splitName2)
            )

From oracle-developer.net it would appear that it can be done like this:

SELECT somethingId, splitId1, splitName1, splitId2, splitName2
FROM (SELECT somethingId, splitId
      FROM SOMETHING JOIN SPLIT ON ... )
      PIVOT ( MAX(splitId)  ,
              MAX(splitName) 
              FOR displayOrder IN (1 AS splitName1, 2 as splitName2)
            )

回复收藏 0 原文

夜深人未静 2024-09-15 05:49:09

我不确定您提供的数据是什么样的，或者您到底想要什么。也许如果您发布了返回您正在查找的数据和/或源数据的定义的查询的解码版本，我们可以更好地回答您的问题。像这样的东西会很有帮助：

create table something (somethingId Number(3), displayOrder Number(3)
   , splitID Number(3));            
insert into something values (1, 1, 10);
insert into something values (2, 1, 11);
insert into something values (3, 1, 12);
insert into something values (4, 1, 13);
insert into something values (5, 2, 14);
insert into something values (6, 2, 15);
insert into something values (7, 2, 16);

create table split (SplitID Number(3), SplitName Varchar2(30));
insert into split values (10, 'Bob');
insert into split values (11, 'Carrie');
insert into split values (12, 'Alice');
insert into split values (13, 'Timothy');
insert into split values (14, 'Sue');
insert into split values (15, 'Peter');
insert into split values (16, 'Adam');

SELECT * 
FROM (
   SELECT somethingID, displayOrder, so.SplitID, sp.splitname
      FROM SOMETHING so JOIN SPLIT sp ON so.splitID = sp.SplitID
)
PIVOT ( MAX(splitId) id,  MAX(splitName) name
   FOR (displayOrder, displayOrder) IN ((1, 1) AS split, (2, 2) as splitname)
);

I'm not sure from what you provided what the data looks or what exactly you would like. Perhaps if you posted the decode version of the query that returns the data you are looking for and/or the definition for the source data, we could better answer your question. Something like this would be helpful:

create table something (somethingId Number(3), displayOrder Number(3)
   , splitID Number(3));            
insert into something values (1, 1, 10);
insert into something values (2, 1, 11);
insert into something values (3, 1, 12);
insert into something values (4, 1, 13);
insert into something values (5, 2, 14);
insert into something values (6, 2, 15);
insert into something values (7, 2, 16);

create table split (SplitID Number(3), SplitName Varchar2(30));
insert into split values (10, 'Bob');
insert into split values (11, 'Carrie');
insert into split values (12, 'Alice');
insert into split values (13, 'Timothy');
insert into split values (14, 'Sue');
insert into split values (15, 'Peter');
insert into split values (16, 'Adam');

SELECT * 
FROM (
   SELECT somethingID, displayOrder, so.SplitID, sp.splitname
      FROM SOMETHING so JOIN SPLIT sp ON so.splitID = sp.SplitID
)
PIVOT ( MAX(splitId) id,  MAX(splitName) name
   FOR (displayOrder, displayOrder) IN ((1, 1) AS split, (2, 2) as splitname)
);

回复收藏 0 原文

~没有更多了~