Transposing the row/columns in SQL

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

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 20-Aug-10 19:41pm

shivaprasadk

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Goutam Patra · Answer 1 · 2010-08-20T22:26:00

Solution 1

Take a look at this [^]

Posted 20-Aug-10 22:26pm

Goutam Patra

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

khunt.dhaval · Answer 2 · 2011-04-30T02:26:00

Solution 2

i think this query will help u

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

Posted 30-Apr-11 2:26am

khunt.dhaval

aravinth04 · Answer 3 · 2011-05-04T01:25:00

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