### WEBINAR:

On-Demand

Application Security Testing: An Integral Part of DevOps

#### 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.