COLUMNS Axis Expression

OLE DB Programmer's Reference

The COLUMNS axis expression consists of the CROSSJOIN function applied on two literal sets. Applying the mappings described in "Literal Sets" generates two virtual tables. These virtual tables are used as input to the SQL statements described in "CROSSJOIN Function."

The result of these operations yields the following two tables (denoted as Table C and Table CPrime):

Name1	Name2	Rank
Products.[All].Office.Access	Geography.[All].USA.Kansas	1
Products.[All].Office.Access	Geography.[All].USA.NewYork.Buffalo	2
Products.[All].Office.Access	Geography.[All].USA.Kansas.Topeka	3
Products.[All].Office.Access	Geography.[All].USA	4
Products.[All].Office.Access	Geography.[All].Canada	5
Products.[All].Office.Word	Geography.[All].USA.Kansas	6
Products.[All].Office.Word	Geography.[All].USA.NewYork.Buffalo	7
Products.[All].Office.Word	Geography.[All].USA.Kansas.Topeka	8
Products.[All].Office.Word	Geography.[All].USA	9
Products.[All].Office.Word	Geography.[All].Canada	10

Product	ProdLine	Product.[All]	City	State	Country	Geography.[All]	Rank
Products.[All].Office.Access	Products.[All].Office	Product.[All]	ALL()	Geography.USA.Kansas	Geography.USA	Geography.[All]	1
Products.[All].Office.Access	Products.[All].Office	Product.[All]	Geography.USA.NewYork.Buffalo	Geography.USA.NewYork	Geography.USA	Geography.[All]	2
Products.[All].Office.Access	Products.[All].Office	Product.[All]	Geography.USA.Kansas.Topeka	Geography.USA.Kansas	Geography.USA	Geography.[All]	3
Products.[All].Office.Access	Products.[All].Office	Product.[All]	ALL()	ALL()	Geography.USA	Geography.[All]	4
Products.[All].Office.Access	Products.[All].Office	Product.[All]	ALL()	ALL()	Geography.Canada	Geography.[All]	5
Products.[All].Office.Word	Products.[All].Office	Product.[All]	ALL()	Geography.USA.Kansas	Geography.USA	Geography.[All]	6
Products.[All].Office.Word	Products.[All].Office	Product.[All]	Geography.USA.NewYork.Buffalo	Geography.USA.NewYork	Geography.USA	Geography.[All]	7
Products.[All].Office.Word	Products.[All].Office	Product.[All]	Geography.USA.Kansas.Topeka	Geography.USA.Kansas	Geography.USA	Geography.[All]	8
Products.[All].Office.Word	Products.[All].Office	Product.[All]	ALL()	ALL()	Geography.USA	Geography.[All]	9
Products.[All].Office.Word	Products.[All].Office	Product.[All]	ALL()	ALL()	Geography.Canada	Geography.[All]	10

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

   In the current example, this is 2.

2. Find the names of each dimension:

   SELECT DISTINCT COMPONENT(Name, -1) FROM C

   In the current 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)

   In the current example, this is 3 for Geography and 2 for Products.

4. Create 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 create 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

1998-2001 Microsoft Corporation. All rights reserved.