Blog : Pivot Example in SQL Server
Pivot Example in SQL Server
Plz. Give me a Pivot Example of Multiple Rows & some Explanation about Pivot
& Syntax.
sql-server-pivot-and-unpivot-table-examples
pivot-tables-in-sql-server-a-simple-sample
getting-started-with-pivot-queries-in-sql-server-20052008
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.
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
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