15,882,209 members
See more: , +
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

Posted

## Solution 1

Take a look at this [^]

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

## Solution 2

i think this query will help u

select Sum(t1),month(TrDt) from tablename group by month(TrDt);

## Solution 3

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

Aravinth.G
SQL Developer,
Chennai
Email:- aravinth.it04@gmail.com