Oracle PIVOT，两次? [英] Oracle PIVOT, twice?

问题描述

我一直试图摆脱使用DECODE在Oracle 11g中透视行的问题，Oracle 11g中有一个方便的PIVOT函数.但我可能发现了一个局限性:

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:

我试图为基表中的每个值返回2列.像这样:

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)
            )

我可以使用DECODE来做到这一点，但是我无法通过语法来让我使用PIVOT来做到这一点.这有可能吗?似乎该函数处理起来并不难.

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.

对于SQL问题，StackOverflow可能不是正确的溢出吗?

is StackOverflow maybe not the right Overflow for SQL questions?

外面有人吗?

推荐答案

来自 oracle -developer.net ，看来可以这样做:

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)
            )

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