Pivot in SQL Server 2008

Definition: Pivot table is a T-SQL operator that converts one tabled-valued expression into another table.
It transforms the values of a column of a table into multiple columns producing Pivot table.Basically, It rotates the rows into columns and performs aggregation on the desired column.

Use: It is used to produce meaningful informtion from the table data easily and quickly. It converts one table format into another.It is great operator for producing summary tables. It is frequently used in Reports for the means of grouping and aggregation of Report Data.

Annotated Syntax:

SELECT
[first pivoted column] AS ,
[second pivoted column] AS ,
...
[last pivoted column] AS
FROM
(SELECT query that produces the data)
AS (alias for the source query)

PIVOT
(
(aggregation function(column being aggregated))
FOR
[column that contains the values that will become column headers]
IN ( [first pivoted column], [second pivoted column],
... [last pivoted column])
) AS
optional ORDER BY clause

Examples:

Static Pivot Table Query:

                                     

























Dynamic PIVOT Table Query:

SQL Server 2008 MERGE Statement

MERGE Statement

What do you do when your application logic requires that you INSERT a record if the record doesn’t exist, or UPDATE the record if it does exist? In SQL Server 2008, It has been simplified the amount of T-SQL code it requires to perform INSERT/UPDATE logic, by implementing the MERGE statement.

You can do more with the MERGE statement then just inserting and updating records as mentioned above. You can also use it to delete records. Another thing you can do with the MERGE statement is to perform a specific number of merge actions based on a TOP clause. The MERGE statement can also generate output that will identify which records where inserted, updated, or deleted.

Following is an example of using MERGE to perform UPDATE and DELETE operations on a table in a single statement

Here I am using AdventureWorks sample database to show this example.

MERGE can be used to update the ProductInventory table in the AdventureWorks sample database on a daily basis, based on orders that are processed in the SalesOrderDetail table. The following MERGE statement updates the Quantity column of the ProductInventory table by subtracting the number of orders placed each day for each product in the SalesOrderDetail table. If the number of orders for a product drops the product's inventory to 0, the row for that product is deleted from the ProductInventory table.

USE AdventureWorks;

GO

IF OBJECT_ID (N'Production.usp_UpdateInventory', N'P') IS NOT NULL DROP PROCEDURE Production.usp_UpdateInventory;

GO

CREATE PROCEDURE Production.usp_UpdateInventory

@OrderDate datetime

AS

MERGE Production.ProductInventory AS target

USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod

JOIN Sales.SalesOrderHeader AS soh

ON sod.SalesOrderID = soh.SalesOrderID

AND soh.OrderDate = @OrderDate

GROUP BY ProductID) AS source (ProductID, OrderQty)

ON (target.ProductID = source.ProductID)

WHEN MATCHED AND target.Quantity - source.OrderQty <= 0

THEN DELETE

WHEN MATCHED

THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty,

target.ModifiedDate = GETDATE()

OUTPUT $action, Inserted.ProductID, Inserted.Quantity, Inserted.ModifiedDate, Deleted.ProductID,

Deleted.Quantity, Deleted.ModifiedDate;

GO

EXECUTE Production.usp_UpdateInventory '20030501'

Advantages of using MERGE statement

1 - With a single statement we can easily implement insert, update, and delete logic to handle criteria for maintaining a table.

2 - The MERGE statement handles all the joining of the source and target. This minimizes the amount of code you need to write to handle merge logic.

3 – It requires less SQL Server resources which make it fast and optimized.

Script to compare data in two tables with identical structure.

These SQL scripts are to compare data in two separate tables with identical structure.
These statement not only work for Microsoft SQL as well as for any other Databases.

Following script finds records which exist in source table but not in target table.

The SQL statements can be modified for more than one column in Primary Key.

In SQL Server 2005 or later, EXCEPT operator can be used.

Following script finds records which exist in source table but not in target table as well as records which exist in target table but not in source table.

But Union operator can slow down the execution for tables with large amounts of data.