Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

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

Learn X-to-Y and Y-to-X axis transformation techniques for showing a table's row vertically or its column horizontally—no special operators or loops needed.


advertisement

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.



Alex Kozak is a senior DBA/analyst working for SAP Canada. He has more than 15 years of database and programming experience. Microsoft has included some of his articles in the MSDN Library.
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap