...

Dynamic Row-to-Column Transposition – Case 2

 
SQL(179)

【Question】

Here’s the source table:

ProductLine  PhoneModel  Production

Line1#      iPhone 6     3200

Line2#      iPhone 6     1400

Line3#      iPhone 6     3000

Line3#      Nubia mini   1600

Line4#      Mi 4        4000

Line4#      iPhone 5S    2000

Line4#      Honor 6     3500

I want to write a SQL to get the form like this. The PhoneModel and Production fields have dynamic values as different product lines may produce more than one phone model.

ProductLine  PhoneModel  Production  PhoneModel  Production  PhoneModel Production

Line1#      iPhone 6     3200

Line2#     iPhone 6     1400

Line3#     iPhone 6     3000        Nubia mini    1600

Line4#     Mi 4        4000        iPhone 5S     2000       Honor 6      3500

 

【Answer】

SQL has no direct support for generating dynamic columns. Oracle PIVOT supports returning certain forms of dynamic columns but it has limits. So a dynamic SQL query needs to be composed to get a dynamic result set. But your case is more than that. We need to find the group with the most members to know the number of columns, and field values do not directly rotated into column headings as most PIVOT operations do. We need to write a stored procedure to get the result set step by step; otherwise the code is difficult to understand and hard to maintain.

A convenient way of doing this is in SPL:

A

B

1

$select ProductLine,PhoneModel,sum(Production)Production   from Production group by ProductLine,PhoneModel

2

=A1.group(ProductLine)

3

=A2.max(~.len())

4

=A3.("PhoneModel"+string(#)+",Production"+string(#)).concat@c()

5

=create(ProductLine,${A4})

6

for   A2

=A6.ProductLine|A6.conj([PhoneModel,Production])

7

=A5.record(B6)

8

result   A5

A1: Group the Production table by ProductLine and PhoneModel and then aggregate each group to calculate Production;

A2: Group the grouped table by ProductLine again;

A3: Get the largest number of phone models produced by a product line;

A4: Compose a string in the form of “PhoneModel 1, Production 1, PhoneModel 2, Production 2,…” according to the largest number of columns;

A5: Create an empty table sequence consisting of these fields: ProductLine, PhoneModel 1, Production 1, PhoneModel 2, Production 2,…;

A6-B7: Loop over each group in A2 to join up PhoneModel and Production into a sequence in the form of [Line3#, iPhone 6, 3000, Numbia mini, 1600,…] and populate it to A5’s table sequence;

You can send the result set to an application. To call the result set in a third application, see .


SQL(179)
 
 •  388 View  •