...

Row-to-Column Transposition – Horizontal & Vertical Aggregation

 
SQL(179)

【Question】

Dept                 Type

Production dept        Desktop PC

R&D                 Laptop

Administration dept     Desktop PC

Production dept.        Desktop PC

R&D                 Laptop

Administration dept     Desktop PC

I want a format like this:

Dept              Desktop PC      Laptop      DeptTotal

Production dept      2              0           2

R&D              0              2           2

Administration dept  2              0           2

Total              4              2           6

 

【Answer】

It’s easy to implement the dynamic row-to-column transposition with a crosstab report in RaqReport to display data at front end. It’s also simple to implement the horizontal and vertical summarization at the reporting-side. To generate a result set in the above format for another application, you can use SPL:

A

1

$select Dept,Type,count(1)  as sum from tb group by Dept, Type

2

=A1.pivot(Dept;Type,sum)

3

= A2.run(~.record(~.array().(if(~,~,0))))

4

=A3.derive(~.array().to(2:).sum():DeptTotal)

5

=A4.record("Total"|(A4.fno()-1).(A4.field(~+1).sum()))

A1: Group data by Dept and Type and count the computers in every group in simple SQL;

A2: Perform row-to-column transposition over A1’s table sequence to generate a new table sequence consisting of Dept, Desktop, Laptop fields;

A3: Change the null sum field value into 0;

A4: Add a DeptTotal column to A3;

A5: Add a Total row to A4L

Here’s the final result set:

Dept

Desktop   PC

Laptop

DeptTotal

Production   dept

2

0

2

R&D

0

2

2

Administration   dept

2

0

2

Total

4

2

6

 

 


SQL(179)
 
 •  364 View  •