Strange Pivot - Concatenate Text Fields [message #613546] |
Wed, 07 May 2014 15:14 |
hdogg
Messages: 94 Registered: March 2007
|
Member |
|
|
Order#, Discount Name, Discount %
1, volume, 5%
1, friend, 30%
1, product7, 5%
Ask you can see, Order number 1 has 3 records that are 3 types of discounts.
I already am very familar with PIVOTING data, I use 10 G which doesn't support PIVOT function
This is what I would like to have the query display:
row, concate_text
1, volume 5% - friend 30% - product7 5%
so basically it creates a massive text field
|
|
|
|
Re: Strange Pivot - Concatenate Text Fields [message #613560 is a reply to message #613546] |
Wed, 07 May 2014 23:25 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Just a few comments on your post:
PIVOT is not a function, but one of transformation techniques which may be achieved without PIVOT clause e.g. by aggregation.
Your expected result is not classical PIVOT, but string aggregation. There are many techniques and LISTAGG introduced in 11gR2 is only one of them.
List of most of them (description with examples) is stated e.g. in this article: http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php
Just pick the one you like most and is available in your Oracle version, only do not use WM_CONCAT, as it is unsupported.
|
|
|
|