View equivalent to Crosstab query in Access

[swilliams]

Hello,
Does anyone know how to create a view in SQL Server 7 which is equivalent to an Access Crosstab query??

TIA
Simon

 

[guljar]

This following is just cut and paste from SQL Server Books Online. So if you have that you can search by cross-tab to find this. Any way, only difference with Access and SQL server is: in SQL server number of columns have to be fixed but in Access it is dynamic.
Hope it will help you..
========================
Cross-tab Reports
Sometimes it is necessary to rotate results so that columns are presented horizontally and rows are presented vertically. This is known as creating a PivotTable®, creating a cross-tab report, or rotating data.

Assume there is a table Pivot that has one row per quarter. A SELECT of Pivot reports the quarters vertically:

Year Quarter Amount

---- ------- ------

1990 1 1.1

1990 2 1.2

1990 3 1.3

1990 4 1.4

1991 1 2.1

1991 2 2.2

1991 3 2.3

1991 4 2.4



A report must be produced with a table that contains one row for each year, with the values for each quarter appearing in a separate column, such as:

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



These are the statements to create the Pivot table and populate it with the data from the first table:

USE Northwind

GO



CREATE TABLE Pivot

( Year SMALLINT,

Quarter TINYINT,

Amount DECIMAL(2,1) )

GO

INSERT INTO Pivot VALUES (1990, 1, 1.1)

INSERT INTO Pivot VALUES (1990, 2, 1.2)

INSERT INTO Pivot VALUES (1990, 3, 1.3)

INSERT INTO Pivot VALUES (1990, 4, 1.4)

INSERT INTO Pivot VALUES (1991, 1, 2.1)

INSERT INTO Pivot VALUES (1991, 2, 2.2)

INSERT INTO Pivot VALUES (1991, 3, 2.3)

INSERT INTO Pivot VALUES (1991, 4, 2.4)

GO



This is the SELECT statement to create the rotated results:

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 Northwind.dbo.Pivot

GROUP BY Year

GO



This SELECT statement also handles a table in which there are multiple rows for each quarter. The GROUP BY combines all rows in Pivot for a given year into a single row in the output. When the grouping operation is being performed, the CASE functions in the SUM aggregates are applied in such a way that the Amount values for each quarter are added into the proper column in the result set and 0 is added to the result set columns for the other quarters.

If the results of this SELECT statement are used as input to a spreadsheet, it is easy for the spreadsheet to calculate a total for each year. When the SELECT is used from an application it may be easier to enhance the SELECT statement to calculate the yearly total, for example:

SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotal

FROM (SELECT Year,

SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS Q1,

SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS Q2,

SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS Q3,

SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4

FROM Pivot AS P

GROUP BY P.Year) AS P1

GO



You may also want to consider GROUP BY with CUBE or with ROLLUP. Both compute the same sort of information, but in a slightly different format.
==================================