Transpose rows into columns.

Hi Guys,
 
I need to convert the data like below by using Actuate.
 
Either in iob level or Report level. Kinldy 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
 
 
--
Thanks & Regards,
Gopi

Find more posts tagged with

Comments

JFreeman

You could manually iterate over your data set and dynamically add columns to a table.
 
Here is an example of dynamic table/columns that will help get you started: <a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?/topic/24254-creating-column-dynamically/?p=132987'>http://developer.actuate.com/community/forum/index.php?/topic/24254-creating-column-dynamically/?p=132987</a>
 
EDIT: More Examples: <a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?/topic/36942-scripted-dataset-to-parse-a-dynamic-xml-string/?p=137936'>http://developer.actuate.com/community/forum/index.php?/topic/36942-scripted-dataset-to-parse-a-dynamic-xml-string/?p=137936</a>

datakeywor

Group records by col1, and then create an empty table according to "the maximum number of recorders in each group plus 1". To perform the transposition , loop through each group to append data to the empty table. It's pretty hard to do this with the stored procedure, but you can make it in esProc using the following the code:
 
A1= select col1,col2 from tb
A2= A1.group(col1)
A3= create(${(A2.max(~.count())+1).("col1"+string(~)).string()})
A4> A2.run(A3.record(~.col1|~.(col2)))
 
In practice , the column names of the new two-dismentional table are usually the values in col2(rather than the meaningless name such as col1 and col2). Find more transposition examples from <a data-ipb='nomediaparse' href='http://blog.raqsoft.com/?p=4962'>http://blog.raqsoft.com/?p=4962</a>. BIRT can access the esProc script via jdbc, and refer to <a data-ipb='nomediaparse' href='http://blog.raqsoft.com/?p=4665'>http://blog.raqsoft.com/?p=4665</a> to know more about this.

PaulCooper

Another way would be to add a row_id column to the SQL:
<pre class="_prettyXprint">
row_number() over (partition by col1 order by <original ordering fields>) "column_id"</pre>
Replace <original ordering fields> with the appropriate fields.
 
Then use a crosstab in the report with "column_id" as your top grouping and col1 as your left grouping.
 
You may not want 1, 2, 3, etc as column headings so you may want to hide this field and create a field that maps the number to the headings you want.