Pivot Example in SQL Server

hi,

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 they are required on any remaining column values that are wanted in the final output.

Example

select
   *
 from
 (
   select
     PS.Name, P.Color, PIn.Quantity
   from Production.Product P
   inner join Production.ProductSubcategory PS
     on PS.ProductSubcategoryID = P.ProductSubcategoryID
   left join Production.ProductInventory PIn
     on P.ProductID = PIn.ProductID
 ) DataTable
 PIVOT
 (
   SUM(Quantity)
   FOR Color
   IN (
     [Black],[Blue],[Grey],[Multi],[Red],
     [Silver],[Silver/Black],[White],[Yellow]
   )
 ) PivotTable

Google is your friend: Be nice and visit him often. He can answer questions a lot more quickly than posting them here...

A very quick search using your subject as the search term gave over 4 million hits: Pivot Table[^]

In future, please try to do at least basic research yourself, and not waste your time or ours.

http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx[^]
http://ask.sqlservercentral.com/questions/23978/pivoting-multiple-rows-into-one-row-with-multiple.html[^]

visit link see question and solution no 1 with example
create pivot table in sql-server[^]
Happy Coding!
:)

Hi Raj,

Please find the below example for Pivot in SQL Server.

SELECT UPPER([name]) AS SALESMAN ,[oil] AS OIL ,[sugar] AS SUGAR,[dall] AS DALL,[rice] AS RICE 
FROM (SELECT name,product,amount FROM sales)ps
pivot
(
SUM(amount)
FOR product in 
([oil],[sugar],[dall] ,[rice])
)AS pvt

http://www.mssqltips.com/sqlservertip/1019/crosstab-queries-using-pivot-in-sql-server/[^]

Example-

Declare @year int
Declare @month int
Set @year=2012
Set @month=2
 
SELECT WeekNumber, [Open],[close],[pending],[inprogress]
FROM
(
Select DATEPART(DW,daterecieving  ) as WeekNumber, current_status,COunt(current_status) as StatusCount from FileRegister
Where DATEPART(YEAR,daterecieving )=@year AND DATEPART(M,daterecieving) =@month GROUP BY DATEPART(DW,daterecieving  ), current_status
) SourceTable
PIVOT
(
COunt(current_status)
FOR current_status IN
( [Open],[close],[pending],[inprogress] )) as OutputTable
Order by OutputTable.WeekNumber