What is PIVOT features in SQL Server , SQL 2005 FAQs -II

Message

PIVOT feature converts row data to column for better analytical view. Below is a simple
PIVOT fired using CTE. Ok the first section is the CTE which is the input and later
PIVOT is applied over it.
WITH PURCHASEORDERHEADERCTE(Orderdate,Status,Subtotal) as
(

Select year(orderdate),Status,isnull(Subtotal,0) from
purchasing.PURCHASEORDERHEADER
)
Select Status as OrderStatus,isnull([2001],0) as 'Yr 2001' ,isnull([2002],0) as 'Yr
2002' from PURCHASEORDERHEADERCTE
pivot (sum(Subtotal) for Orderdate in ([2001],[2002])) as pivoted
You can see from the above SQL the top WITH statement is the CTE supplied to the
PIVOT. After that PIVOT is applied on subtotal and orderdate.

Author	Message
kirankumar.prkk Moderator Group Joined: 17 Oct 2007 Location: India Online Status: Offline Posts: 30004
	PIVOT feature converts row data to column for better analytical view. Below is a simple PIVOT fired using CTE. Ok the first section is the CTE which is the input and later PIVOT is applied over it. WITH PURCHASEORDERHEADERCTE(Orderdate,Status,Subtotal) as ( Select year(orderdate),Status,isnull(Subtotal,0) from purchasing.PURCHASEORDERHEADER ) Select Status as OrderStatus,isnull([2001],0) as 'Yr 2001' ,isnull([2002],0) as 'Yr 2002' from PURCHASEORDERHEADERCTE pivot (sum(Subtotal) for Orderdate in ([2001],[2002])) as pivoted You can see from the above SQL the top WITH statement is the CTE supplied to the PIVOT. After that PIVOT is applied on subtotal and orderdate. Posted: 02 Feb 2007 at 1:00am

	IP Logged

SQL 2005 FAQs -II
GenProgrammers : Database : SQL 2005 FAQs -II