【Question】

Here’s a table:
Item    Location    Quantity
Item A  Location 1   1000
Item A  Location 2   2000
Item B  Location 1   4000
Item B  Location 3   1000
Item C  Location 2   1500
Values of Location field are in a fixed range: (Location 1, Location 3)
1. The first table I want to get in SQL looks like this:
Time    Location 1    Location 2    Location 3
System time  5000  3500  1000

This is to count the quantity of all items in each location at a certain system time. For instance, there are 1000 item A and 4000 item B at location 1 in the source table and the quantity of all items at location 1 is 1000+4000=5000.
2. The second table I want in SQL looks like this:
Time    Item A    Item B    Item C
System time  3000  5000  1500

This is to count the quantity of each item in all the locations at a certain time. For instance, there are 3000 Item A in location 1 and location 2 in the source table.
Is there anybody who can give some tips about my problems? Thanks.

【Answer】

Both problems are to perform a column-to-row transposition to generate a result set with dynamic column values. The Oracle PIVOT clause allows you to perform a transposition but it doesn’t support dynamic result sets. Writing dynamic SQL stored procedure is also complicated and hard to debug. So the common way is retrieving the source data to handle it. To build a report, you can do it with any reporting tool that supports crosstab reports by retrieving the data to handle it on the presentation interface, like the RaqReport. In RaqReport you can group data and expand it horizontally.

If you need to prepare the data source for an application, you can do it in SQL, like this:

 A1: Group tb table by Item and sum each group;

A2: Generate a new table sequence consisting of Time, Item A, Item B, Item C…;

The value of Time field is the current system time, which is now();

Values of Item A, Item B, Item C… are their total quantities. Expression A1.(string(Quantity)+”:”+Item).concat@c() composes strings like 3000:Item A,5000:Item B,1500:Item C,…, and generate a new table sequence with dynamic columns with the macro replacement ${}.

Here’s the result of A2: