RaqForum 28 No.
413 View  • 
     ...

Report Building with Column-to-Row & Row-to-Column transpositions

 
SQL(179)

【Question】

Could you please help me with transposing data from column to row in reports as shown in attachments:

undefined

 

【Answer】

My suggestion is that it would be best to prepare data before presenting it with the reporting tool. You can achieve a dynamic result set with Java, SQL or a stored procedure. But all of them generate complicated code. Try using SPL to handle it in simple code:

A

B

1

=myDB1.query("select *   from kpi  order by   f_sitename,dataset_date")

2

=A1.id(dataset_date)

=A1.fname().to(3,)

3

=create(site,'KPI  Name',${A2.concat@cq()})

4

for A1.group(f_sitename)

= A4.align(A2,dataset_date)

5

>B2.run(A3.record([A4.f_sitename,~]|B4.field(~)))

6

result A3

 

A1: Retrieve data in SQL and sort it by f_sitename and dataset_date;

A2: Get distinct datetime values;

B2: Get all field names from A1’s table sequence and return them beginning from the third one;

A3: Create the result set;

A4-B5: Loop f_sitename values to write them into A3’s result set;

A6: Return A3’s result set.


SQL(179)
 
 •  413 View  •