i need help regarding a SQL Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

i need help regarding a SQL Query

Author

Topic

murthybsnvsn
Starting Member

3 Posts

Posted - 2007-05-01 : 09:34:42

Sql Command to Convert Row of Data Into Column

Assume that you are having the data like this

SalesMan Year SaleAmount
==================================
Carole 2001 1,500
Carole 2002 2,500
Carole 2003 3,500
Carole 2004 4,500
Angle 2001 5,000
Angle 2002 7,500
Angle 2003 10,500
Angle 2004 12,750

Now You Want to Convert the Above Data Like Below :-
=================================================

SalesMan Sal2001 Sal2002 Sal2003 Sal2004
=========================================================
Carol 1,500 2,500 3,500 4,500
Angle 5,000 7,500 10,500 12,750

please help me in this regard

Thanks and Regards,
Murthy.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-01 : 10:15:49

[code]
select SalesMan,
Sal2001 = sum(case when Year = 2001 then SalesAmount else 0 end),
Sal2002 = sum(case when Year = 2002 then SalesAmount else 0 end),
Sal2003 = sum(case when Year = 2003 then SalesAmount else 0 end),
Sal2004 = sum(case when Year = 2004 then SalesAmount else 0 end)
from table
group by SalesMan
[/code]

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-01 : 17:12:02

SQL SERVER 2005 Style



Declare @table Table 
(
	Salesman varchar(100),
	Years int,
	SaleAmount int
)

Insert @table 
Select 'Carole',2001,1500
Union all
Select 'Carole',2002,2500
Union all
Select 'Carole',2003,3500
Union all
Select 'Carole',2004,4500
Union all
Select 'Angle',2001,5000
Union all
Select 'Angle',2002,7500
Union all
Select 'Angle',2003,10500
Union all
Select 'Angle',2004,12750

SELECT  * FROM 
(
	SELECT SalesMan,SaleAmount,Years FROM @Table 
) o
 PIVOT 
(
	Sum(SaleAmount) FOR Years IN 
		(
			[2001],[2002],[2003],[2004]
		)
) p

Chirag

http://chirikworld.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-01 : 18:10:41

Look at the two execution plans.
One is more efficient than the other.

Peter Larsson
Helsingborg, Sweden

EDIT: Never mind. There is no difference on this small set of data.

murthybsnvsn
Starting Member

3 Posts

Posted - 2007-05-02 : 01:33:49

hi peter,

Greetings and Very Good Morning,

thank you very much,

thanks for your valuable time

Thanks and Regards
Murthy.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-02 : 01:37:09

I am amazed no one ever pointed out one of the most popular answers of all time for such type of questions:

DO IT IN THE FRONT-END !!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-02 : 01:48:04

quote:
Originally posted by harsh_athalye

I am amazed no one ever pointed out one of the most popular answers of all time for such type of questions:

DO IT IN THE FRONT-END !!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SO YOU DID IT