Advertisements

Archive

Archive for the ‘SQL Server 2005’ Category

MS SQL Server 2005 – New Feature | PIVOT and UNPIVOT

April 12, 2009 7 comments

Using PIVOT and UNPIVOT Operator

You can use the PIVOT and UNPIVOT relational operators to manipulate a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where necessary on any remaining column values that are desired in the final output. UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

PIVOT provides syntax that is simpler and more readable than what may otherwise be specified in a complex series of SELECT…CASE statements.
A common scenario where PIVOT can be useful is when you want to generate cross-tabulation reports to summarize data. For example, suppose you want to query the PurchaseOrderHeader table in the AdventureWorks sample database to determine the number of purchase orders placed by certain employees. The following query provides this report, broken down by vendor:

USE AdventureWorks;
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID
Output:
VendorID Emp1 Emp2 Emp3 Emp4 Emp5
1 	 4    3    5    4    4
2 	 4    1    5    5    5
3 	 4    3    5    4    4
4 	 4    2    5    5    4
5 	 5    1    5    5    5

This means that the unique values returned by the EmployeeID column themselves become fields in the final result set. As a result, there is a column for each EmployeeID number specified in the pivot clause — in this case employees 164, 198, 223, 231, and 233. The PurchaseOrderID column serves as the value column, against which the columns returned in the final output, called the grouping columns, are grouped. In this case, the grouping columns are aggregated by the COUNT function. Note that a warning message appears indicating that any NULL values appearing in the PurchaseOrderID column were not considered when computing the COUNT for each employee.

UNPIVOT performs almost the reverse operation of PIVOT, by rotating columns into rows. Suppose the table produced in the example above is stored in the database as pvt, and you want to rotate the column identifiers Emp1, Emp2, Emp3, Emp4, and Emp5 into row values that correspond to a particular vendor. This means that you must identify two additional columns. The column that will contain the column values you are rotating (Emp1, Emp2,…) will be called Employee, and the column that will hold the values that currently reside under the columns being rotated will be called Orders. These columns correspond to the pivot_column and value_column, respectively, in the Transact-SQL definition. The query looks like this.

Create the table and insert values as portrayed in the above example:

CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int)
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4)
INSERT INTO pvt VALUES (2,4,1,5,5,5)
INSERT INTO pvt VALUES (3,4,3,5,4,4)
INSERT INTO pvt VALUES (4,4,2,5,5,4)
INSERT INTO pvt VALUES (5,5,1,5,5,5)
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt
GO
Output:
VendorID Employee Orders
1 	 Emp1 	  4
1 	 Emp2 	  3
1 	 Emp3 	  5
1 	 Emp4 	  4
1 	 Emp5 	  4
2 	 Emp1 	  4
2 	 Emp2 	  1
2 	 Emp3 	  5
2 	 Emp4 	  5
2 	 Emp5 	  5
...

Note: UNPIVOT is not the exact reverse of PIVOT. PIVOT performs an aggregation and hence merges possible multiple rows into a single row in the output. UNPIVOT does not reproduce the original table-valued expression result because rows have been merged. Besides, NULL values in the input of UNPIVOT disappear in the output, whereas there may have been original NULL values in the input before the PIVOT operation.

Reference taken from MS BOL: http://msdn.microsoft.com/en-us/library/ms177410.aspx

Advertisements

MS SQL Server 2005 – New Feature | Create Synonyms

April 5, 2009 2 comments

A SYNONYM is a database object that serves the following purposes:

– It provides an alternative name for another database object, referred to as the base object, that can exist on a local or remote server.
– It provides a layer of abstraction that protects a client application from changes made to the name or location of the base object.

For example: consider the Employee table of Adventure Works, located on a server named Server1. To reference this table from another server, Server2, a client application would have to use the four-part name, Server1.AdventureWorks.Person.Employee. Also, if the location of the table were to change, for example, to another server, the client application would have to be modified to reflect that change.

To address both these issues, you can create a synonym, EmpTable, on Server2 for the Employee table on Server1. Now, the client application only has to use the single-part name, EmpTable, to reference the Employee table. Also, if the location of the Employee table changes, you will have to modify the synonym, EmpTable, to point to the new location of the Employee table. Because there is no ALTER SYNONYM statement, you first have to drop the synonym, EmpTable, and then re-create it with the same name, but point it to the new location of Employee.

CREATE SYNONYM:
Syntax-
CREATE SYNONYM [ schema_name_1. ] synonym_name FOR :: = {
[ server_name.[ database_name ] . [ schema_name_2 ]. database_name .
[ schema_name_2 ]. schema_name_2. ] object_name
}

Arguments:
schema_name_1 : Specifies the schema in which the synonym is created. If schema is not specified, SQL Server 2005 uses the default schema of the current user.
synonym_name : Is the name of the new synonym.
server_name : Is the name of the server on which base object is located.
database_name : Is the name of the database in which the base object is located. If database_name is not specified, the name of the current database is used.
schema_name_2 : Is the name of the schema of the base object. If schema_name is not specified the default schema of the current user is used.
object_name : Is the name of the base object that the synonym references.

Example:

-- Create a synonym for the Product table in AdventureWorks.
CREATE SYNONYM MyProduct
FOR AdventureWorks.Production.Product

-- Query the Product table by using the synonym.
SELECT ProductID, Name
FROM MyProduct
WHERE ProductID = 5
Categories: SQL Server 2005 Tags: