SQL Server 2005 introduces support for the PIVOT and UNPIVOT operators. These operators allow data to be viewed in different dimensions and allow aggregations to be displayed along those dimensions.
Create a Management Studio Project.
Click Start | All Programs | Microsoft SQL Server 2005 | SQL Server Management Studio. If asked to connect, enter SERVER Name and authentication details. Then click Connect.
Click File | New | Project.
In the New Project dialog box, click SQL Server Scripts.
Change the Name of the project.
Clear the Create directory for Solution checkbox.
Click OK to open the project.
Create a table and populate it with data.
In the Solution Explorer, right-click Queries and then click New Query.
When prompted for connection information, enter SERVER Name and authentication details. Then click Connect.
Under Queries, right-click the new script file and click Rename.
Rename the script.
In the query editor, type the following code to create and populate a table.
CREATE TABLE SalesOrderTotalsMonthly
(
CustomerID int NOT NULL,
OrderMonth int NOT NULL,
SubTotal money NOT NULL
)
GO
INSERT SalesOrderTotalsMonthly(1,1,5500)
INSERT SalesOrderTotalsMonthly(1,2,1200)
INSERT SalesOrderTotalsMonthly(1,3,1700)
INSERT SalesOrderTotalsMonthly(1,4,2500)
INSERT SalesOrderTotalsMonthly(2,1,1500)
INSERT SalesOrderTotalsMonthly(2,2,5500)
INSERT SalesOrderTotalsMonthly(2,3,5000)
INSERT SalesOrderTotalsMonthly(2,4,6500)
INSERT SalesOrderTotalsMonthly(2,5,500)
INSERT SalesOrderTotalsMonthly(3,1,1100)
INSERT SalesOrderTotalsMonthly(3,2,9000)
INSERT SalesOrderTotalsMonthly(3,3,6500)
INSERT SalesOrderTotalsMonthly(4,1,1200)
INSERT SalesOrderTotalsMonthly(4,2,1500)
INSERT SalesOrderTotalsMonthly(4,3,900)
INSERT SalesOrderTotalsMonthly(4,4,7500)
Create a query that uses the PIVOT operator.
In the query editor, type the following query that will pivot the table so that the sales for each month are output by customer.
SELECT * FROM SalesOrderTotalsMonthly
PIVOT (SUM(SubTotal) FOR OrderMonth IN
([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) AS a
Select the statements you just typed.
Press F5 to execute the query.
You should see a row for each customer and columns for each month across the top of the results. The customer's total order amounts for a given month are displayed.
|
No responses found. Be the first to respond and make money from revenue sharing program.
|