RaqForum 28 No.
521 View  • 
     ...

Dynamic Row-to-Column Transposition – Case 3

 
SQL(179)

【Question】

Hi Guys,

I need to convert data like below using Actuate. Either in iob level or Report level. Kindly help us to achieve this.

Source:

col1, col2

1,10

1,20

1,30

2,10

2,40

3,70

3,60

3,10

Target:

col1,  col2,  col3,  col4,  col5

1    10    20    30    40

2    10    40

3    70    60    10

 

【Answer】

It’s complicated to handle row-to-column transposition in the stored procedure. Yet it’s simple to get it done in SPL:

A

1

$select col1,col2 from tb

2

=A1.group(col1)

3

=create(${(A2.max(~.count())+1).("col"+string(~)).concat@c()})

4

>A2.run(A3.record(~.col1|~.(col2)))

A1: Retrieve data in SQL;

A2: Group data by col1;

A3: Create an empty table where the number of columns is the max number of records plus 1;

A4: Loop over each group to populate data into the empty table;

In many cases in real-world businesses, the field names of the new two-dimensional table are values of col2 instead of the meaningless col1, col2. 


SQL(179)
 
 •  521 View  •