I have an iSeries server running V5R4. I need to flatten a row into columns. Here's an example of the data:
Sequence Ordn Ordl Percentage
1 0140766 1 0
2 0140766 1 30
3 0140766 1 7
4 0140766 1 3
1 0140766 2 0
2 0140766 2 30
3 0140766 2 2
Sequence is the order in which the percentages should be calculated
Ordn is the customer's order number
Ordl is the order line number
Percentage is a list of percentages off list price
The number of rows (or percentages) can vary from 1 - 5 for any given order line.
The file would need to be grouped on order number and sorted first on order line number and then sequence.
I need to flatten this file so that it displays in the following way:
Ordn Ordl Perc1 Perc2 Perc3 Perc4 Perc5
0140766 1 0 30 7 3 Null
0140766 2 0 30 2 Null Null
Can anyone help? I have tried several things but nothing works the way I want and my SQL experience is very limited.
Here's a way to manually pivot the data into five sequence columns. It assumes there are only five possible sequences. Also, if you have more than one row with a given sequence, the percentages will be added together.
select
ordn
, ordl
, sum(case when sequence=1 then percentage else null end) as perc1
, sum(case when sequence=2 then percentage else null end) as perc2
, sum(case when sequence=3 then percentage else null end) as perc3
, sum(case when sequence=4 then percentage else null end) as perc4
, sum(case when sequence=5 then percentage else null end) as perc5
from yourtable
group by ordn, ordl
order by ordn, ordl
(Note that the else null clauses are not strictly necessary here, since null is the default value of a case expression that lacks an else case.)