COLUMNS Axis Expression

The COLUMNS axis expression consists of the CROSSJOIN function applied on two literal sets. By applying the mappings described in "Literal Sets," you get two virtual tables. These virtual tables are used as input to the SQL statements described in "CROSSJOIN." The result of these operations yields table C:

Denote by Cprime the following table:

Cprime is obtained from C by using the following steps:

1. Find out the number of nested dimensions in the axis expression:

   SELECT COUNT DISTINCT COMPONENT(Name, -1) FROM C
   

   For our example, this is 2.

2. Find the names of each dimension:

   SELECT DISTINCT COMPONENT(Name, -1) FROM C
   

   For our example, this is Products, Geography.

3. For each dimension, find out the deepest member in the axis expression:

   SELECT MAX(LEVEL(Name)) FROM C GROUP BY COMPONENT(Name, -1)
   

   For our example, this is 3 for Geography and 2 for Products.

4. Make two tables — C1 and C2. C1 has all members in C from Products and C2 has all members in C from Geography.

   CREATE LOCAL TEMPORARY VIEW C1(Name, Rank) AS
   
      SELECT Name, NewRank AS Rank 
   FROM 
        (SELECT Name1 FROM C WHERE COMPONENT(Name, -1) = "Products"
        RANK ROWS AS NewRank RANKORDER BY Rank)
   
   CREATE LOCAL TEMPORARY VIEW C2(Name, Rank) AS
   
      SELECT Name, NewRank AS Rank 
   FROM 
        (SELECT Name2 FROM C WHERE COMPONENT(Name, -1) = “Geography”
             RANK ROWS AS NewRank RANKORDER BY Rank)
   

5. Now make Cprime:

   SELECT Product AS COMPONENT(C1.Name, 2), 
      ProdLine AS COMPONENT(C1.Name, 1), 
      Product.All AS COMPONENT(C1.Name, 0),
      City AS COMPONENT(C2.Name, 3),
      State AS COMPONENT(C2.Name, 2),
      Country AS COMPONENT(C2.Name, 1),
      Geography.All AS COMPONENT(C2.Name, 0)
      NewRank AS Rank
   FROM 
      (C1 CROSS JOIN C2) RANK ROWS AS NewRank 
   RANKORDER BY C1.Rank, C2.Rank