...

Dynamic Row-to-Column Transposition with Duplicate Field Values

 
SQL(179)

【Question】

Product

Parameter

Price

Date

Crank A

Medium

4.3

5.1

Crank A

Medium

4.2

5.1

Outer layer

Thick

7

5.3

Gear

7 teeth

10

5.5

 

My problem is this: In Oracle 11G, how to transpose Date values into column names and make Price values the intersection values without summing the prices of two Crank As? With my code of transposing row to column, Crank A’s prices are summed into 8.5 and two Crank As become one row.

 

【Answer】

This is a dynamic row-to-column transposition. It’s complicated to do it in SQL. It’s easy to get it done in SPL:

A

B

1

$select Product,Parameter,Price,Date   from tb order by Product,Date

2

=A1.id()Date

3

=create(Product,Parameter,${A2.concat@c()})

4

for  A1

>A3.record(A4.Product|A4.Parameter|(A2.pos(A4.Date)-1).(null)|A4.Price)

 

A1: Execute a SQL query to retrieve data and group it by Product and Date;

A2: Get distinct Date values;

A3: Create an empty table sequence for holding final result set according to the Date value;

A4-B4: Loop A1’s groups to insert Product, Parameter and Price values into A3’s table sequence:

 undefined

 


SQL(179)
 
 •  442 View  •