1

I'm having some trouble with formatting so hopefully my description makes sense. I am writing a query for an Oracle DB that doesn't support the Pivot function, but I have used Decode in the past to achieve a similar result.

The query so far returns a table of 2 columns, the first is a text label, the second is an item number. Every row will contain a label and an item number, the item numbers will each be unique but the labels will repeat.

For example 

A - 101  
B - 102  
A - 103  
C - 104  
B - 105  
A - 106

I would like to pivot this so I have one row for each text label and as many columns as necessary to show all the matching item numbers. So the table would look like

A - 101 - 103 - 106  
B - 102 - 105  
C - 104

The quantity of numbers that need to be pivotted is variable, as are the item numbers themselves. Is this possible to do using the decode function, or any other function?

sstan
  • 35,425
  • 6
  • 48
  • 66
user2994502
  • 33
  • 3
  • I think you will need a custom function for that, q.v. [here](http://stackoverflow.com/questions/12145379/how-to-retrieve-two-columns-data-in-a-b-format-in-oracle). And it would also make life easier if you were to have separate columns for the letters and numbers. – Tim Biegeleisen Dec 16 '15 at 04:07
  • what Oracle version u use? – Avi Dec 16 '15 at 04:25
  • Oracle version 10.2.0.5 That other post does look like the same thing, I'll have a read through it – user2994502 Dec 16 '15 at 04:31
  • 2
    It looks like you are wanting to do a string aggregation rather than a pivot. Pivot turns rows into distinct columns, unpivot turns columns into distinct rows. You can check out this article over at [ORACLE-BASE](https://oracle-base.com/articles/misc/string-aggregation-techniques) which shows several methods of string aggregation that will work in pre 11i versions of oracle. – Sentinel Dec 16 '15 at 05:50

1 Answers1

0

Sentinel's link in the comments contained the answer I was looking for, see here ORACLE-BASE

My database version 10.2 does not have the LISTAGG function but it does have the WM_CONCAT function so my query becomes

select label, WM_CONCAT(item_number)  
from table  
group by label
user2994502
  • 33
  • 3