How do I create a cross-tab (or "pivot") query?

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

 

---

Now, you may want to do something more complex, or as mentioned above, produce results a little more dynamically based on the data. Here are some links to code samples: 
 
    Dynamic Crosstab Queries 
 
    Microsoft SQL Server - Dynamic Cross-Tab... 
 
    A simple way to perform crosstab operati... 
 
There are also some third party applications available to ease the work required (and surely Crystal Reports and Excel could be programmed to handle this case as well): 
 
    Rac for SQL2K 
 
    Sql.Net 
 
    The Query Tool 
 
    xp_ags_crosstab 
 
If you know of others, please let us know so we can add them to our list.