Cross-tab (or "pivot") query in SQL
You may find that your data is not well-suited for what you might term as pivot or cross-tab reports. Now, while I believe that such pivoting functionality belongs at the presentation tier and not the data tier, there are many ways to generate reports like this. Here is a simple example that takes normalized sales figures by year and quarter, and produces one row and four columns per year. Run this in Query Analyzer and the output should be pretty self-explanatory:
| SET ANSI_WARNINGS OFF
SET NOCOUNT ON
CREATE TABLE dbo.SalesByQuarter
(
Y INT,
Q INT,
sales INT,
PRIMARY KEY (Y,Q)
)
GO
INSERT dbo.SalesByQuarter(Y,Q,Sales)
SELECT 2003, 2, 479000
UNION SELECT 2003, 3, 321000
UNION SELECT 2003, 4, 324000
UNION SELECT 2004, 1, 612000
UNION SELECT 2004, 2, 524000
UNION SELECT 2004, 3, 342000
UNION SELECT 2004, 4, 357000
UNION SELECT 2005, 1, 734000
GO
SELECT Y,
Q1 = SUM(CASE WHEN Q=1 THEN Sales END),
Q2 = SUM(CASE WHEN Q=2 THEN Sales END),
Q3 = SUM(CASE WHEN Q=3 THEN Sales END),
Q4 = SUM(CASE WHEN Q=4 THEN Sales END)
FROM
dbo.SalesByQuarter
GROUP BY Y
ORDER BY Y
GO
DROP TABLE dbo.SalesByQuarter
GO |
SQL Server 2005 introduces the new PIVOT keyword, however I think it will be a disappointment to most. What people have been searching for is a very dynamic way to pivot, so that you don't have to know all of the potential column headers beforehand. Again, I still feel that the best place to rotate this data is on the client. But I know that some people out there are stubborn and/or don't understand the difference.
In any case, here is the above query in SQL Server 2005 style:
| CREATE TABLE dbo.SalesByQuarter
(
Y INT,
Q INT,
sales INT,
PRIMARY KEY (Y,Q)
)
GO
INSERT dbo.SalesByQuarter(Y,Q,Sales)
SELECT 2003, 2, 479000
UNION SELECT 2003, 3, 321000
UNION SELECT 2003, 4, 324000
UNION SELECT 2004, 1, 612000
UNION SELECT 2004, 2, 524000
UNION SELECT 2004, 3, 342000
UNION SELECT 2004, 4, 357000
UNION SELECT 2005, 1, 734000
GO
SELECT Y,
[1] AS Q1,
[2] AS Q2,
[3] AS Q3,
[4] AS Q4
FROM
(SELECT Y, Q, Sales
FROM SalesByQuarter) s
PIVOT
(
SUM(Sales)
FOR Q IN ([1],[2],[3],[4])
) p
ORDER BY [Y]
GO
DROP TABLE dbo.SalesByQuarter
GO |
