views:

41

answers:

0

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?