Click here to Skip to main content
14,695,406 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 [^]
   
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);
   
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:-

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

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