X- and Y-Axis Transformations in SQL Server : Page 4

Cross-Tab Queries and Axes Transformations

You may be wondering what the difference (or similarity) is between the new PIVOT (UNPIVOT) operators introduced in SS2005 and the axes transformation techniques in this article. Well, the PIVOT (UNPIVOT) operators are more suitable for cases when you need to aggregate data and present the result as a cross-tab report. You can get the same results using the SELECT...CASE technique, the method that was commonly used to generate cross-tab queries in pre-SS2005 versions of SQL Server. However, PIVOT (UNPIVOT) operators are simpler and more flexible. The axes transformation technique does offer the same functionality:

-- Classical SELECT...CASE solution from BOL (SS2000)
CREATE TABLE tblPivot (Year SMALLINT,
                       Quarter TINYINT, 
                       Amount DECIMAL(2,1));
GO
INSERT INTO tblPivot VALUES (1990, 1, 1.1);
INSERT INTO tblPivot VALUES (1990, 2, 1.2);
INSERT INTO tblPivot VALUES (1990, 3, 1.3);
INSERT INTO tblPivot VALUES (1990, 4, 1.4);
INSERT INTO tblPivot VALUES (1991, 1, 2.1);
INSERT INTO tblPivot VALUES (1991, 2, 2.2);
INSERT INTO tblPivot VALUES (1991, 3, 2.3);
INSERT INTO tblPivot VALUES (1991, 4, 2.4);
GO
SELECT Year, 
       SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
       SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
       SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
       SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
   FROM tblPivot
   GROUP BY Year;

-- Result:
Year   Q1   Q2   Q3   Q4
1990   1.1  1.2  1.3  1.4
1991   2.1  2.2  2.3  2.4 

-- Solution with PIVOT operator
SELECT Year, [1] AS Q1, [2] AS Q2, [3] AS Q3, [4] AS Q4
   FROM tblPivot
PIVOT
(SUM(Amount)
FOR Quarter IN
([1],[2],[3],[4]) 
) AS pvt;

-- Result:
Year   Q1   Q2   Q3   Q4
1990   1.1  1.2  1.3  1.4
1991   2.1  2.2  2.3  2.4 

-- Solution with the axes transformations
DECLARE @a1 DEC(2,1), @a2 DEC(2,1), @a3 DEC(2,1), @a4 DEC(2,1), 
        @a5 DEC(2,1), @a6 DEC(2,1), @a7 DEC(2,1), @a8 DEC(2,1);
SELECT @a1 = 0,@a2 = 0,@a3 = 0,@a4 = 0,@a5 = 0,@a6 = 0,@a7 = 0,@a8 = 0;
SELECT @a1 = CASE 
       WHEN quarter = 1 AND Year = 1990 THEN @a1 + amount ELSE @a1 END,
       @a2 = CASE 
       WHEN quarter = 2 AND Year = 1990 THEN @a2 + amount ELSE @a2 END,
       @a3 = CASE 
       WHEN quarter = 3 AND Year = 1990 THEN @a3 + amount ELSE @a3 END,
       @a4 = CASE 
       WHEN quarter = 4 AND Year = 1990 THEN @a4 + amount ELSE @a4 END,
       @a5 = CASE 
       WHEN quarter = 1 AND Year = 1991 THEN @a5 + amount ELSE @a5 END ,
       @a6 = CASE 
       WHEN quarter = 2 AND Year = 1991 THEN @a6 + amount ELSE @a6 END ,
       @a7 = CASE 
       WHEN quarter = 3 AND Year = 1991 THEN @a7 + amount ELSE @a7 END ,
       @a8= CASE 
       WHEN quarter = 4 AND Year = 1991 THEN @a8 + amount ELSE @a8 END
   FROM tblPivot;
SELECT Year = 1990, Q1 = @a1, Q2 = @a2, Q3 = @a3, Q4 = @a4
UNION ALL
SELECT 1991, @a5, @a6, @a7, @a8;

-- Result:
Year   Q1   Q2   Q3   Q4
1990   1.1  1.2  1.3  1.4
1991   2.1  2.2  2.3  2.4 

As you can see, the most elegant solution for generating cross-tab queries is a PIVOT solution. The solution with the axes transformation gives you the same result, but it looks pretty messy and has some limitations (e.g., number of variables equal to Years times Quarters). So, the axes transformation techniques described in this article are more suitable for tasks such as table axes rotations, user-defined aggregations, row-by-row processing without cursors (loops), and many others that are too numerous to include in one article.

Therefore, the axes transformation techniques don't replace or compete with PIVOT (UNPIVOT) operators. Rather, they give you additional functionality and flexibility.

Trusty Old Tricks

I started to using X-to-Y and Y-to-X transformation techniques a long time ago, and found they simplified my code and reduced development time. They also allowed me to apply a new approach to some well-known standard tasks. I'm sure they will do the same for you.

Previous Page