Click here to Skip to main content
15,882,209 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Dear all,

I have a scenario where I need to calculate the Monthwise Purchases, sales and Profit/Loss. This thing is taken care of. Now I need to Transpose this data to show Month & year as column heading and Purchases, sales & Profit/Loss. as Row heads. The Month & year are not fixed.

I know that, it can be done using long method by using temp/variable table. The Pivot option in the SQL supports single data type values.

But, I would like to know about any other method which directly helps in transposing the rows & columns

Thanks in advance
Posted

Take a look at this [^]
 
Share this answer
 
Comments
shivaprasadk 21-Aug-10 5:18am    
Dear Goutam

Saw the example in detail.
Though it is good one and meets the purpose, I am searching for li'l advanced one (may be like a single T-Sql statement to transpose the rows & columns)

thanks
i think this query will help u

select Sum(t1),month(TrDt) from tablename group by month(TrDt);
 
Share this answer
 
Hi Friend,
I have found a solution.
We can do it by using PIVOT Concept.

I'm having a table named as Purchase with columns(ID,DATE,Purchased,Sold,ProfitOrLoss)
I have created a stored procedure with a parameter @Year
I'm going to show the month wise details of total number of purchased items,Total number of Sold items and Average Profit/Loss for a year

Code:-

SQL
--SP_GET_PURCHASE_ORDER 2011
ALTER PROCEDURE SP_GET_PURCHASE_ORDER
@YEAR INT
AS
BEGIN
SELECT 'PURCHASE' AS PURCHASE,JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC FROM(
SELECT PURCHASE,SUBSTRING(CONVERT(VARCHAR(8),DATE,100),1,3) ADATE FROM DBO.PURCHASE WHERE YEAR(DATE)=@YEAR
)V
PIVOT(SUM(PURCHASE) FOR ADATE IN(JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC)) AS PVT

UNION ALL

SELECT 'SALES' AS PURCHASE,JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC FROM(
SELECT SALES,SUBSTRING(CONVERT(VARCHAR(8),DATE,100),1,3) ADATE FROM DBO.PURCHASE WHERE YEAR(DATE)=@YEAR
)V
PIVOT(SUM(SALES) FOR ADATE IN(JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC)) AS PVT

UNION ALL


SELECT 'Profit/Loss' AS PURCHASE,JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC FROM(
SELECT PROFITLOSS,SUBSTRING(CONVERT(VARCHAR(8),DATE,100),1,3) ADATE FROM DBO.PURCHASE WHERE YEAR(DATE)=@YEAR
)V
PIVOT(AVG(PROFITLOSS) FOR ADATE IN(JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC)) AS PVT

END



--Please Keep me in Touch

Aravinth.G
SQL Developer,
Kadamba Technologies Pvt.Ltd,
Chennai
Email:- aravinth.it04@gmail.com
 
Share this answer
 

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