Click here to Skip to main content
15,307,395 members
Please Sign up or sign in to vote.
1.16/5 (5 votes)
See more:
Plz. Give me a Pivot Example of Multiple Rows & some Explanation about Pivot
& Syntax.
Posted
Comments
__TR__ 19-Feb-13 3:22am
   
Have you tried Google[^] ?
OriginalGriff 19-Feb-13 3:46am
   
Reason for my vote of one: Too lazy to use google

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

SQL
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
   
v2
Comments
RedDk 19-Feb-13 16:05pm
   
Sweet ... thanks for this.
Arunprasath Natarajan 11-Dec-13 1:44am
   
I am getting only one value
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.
   
Comments
Raj Nagar 19-Feb-13 4:19am
   
I have Search on Google but i don't get satisfied answer so ask.
if you not want to give answer then why read question.
plz. don't give me a answer again & thank you for Sending this Link....
visit link see question and solution no 1 with example
create pivot table in sql-server[^]
Happy Coding!
:)
   
v2
Hi Raj,

Please find the below example for Pivot in SQL Server.

SQL
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
   
Example-
SQL
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
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900