swap columns and rows using pivot concept in sql server 2005

Question

swap columns and rows using pivot concept in sql server 2005

sandeep.paruchuru

Newbie

Points: 3
More actions
January 23, 2011 at 11:39 pm

#97795

Hi to all,
i want small requirement that is, i have a sample table in sql server database like this..
empid empname designation salary address
----------- -------------------- -------------------- --------------------- --------------------
101 sandeep Trainee 2000.00 Begumpet
102 susheel developer 3000.00 Manikonda
103 santhosh developer 4000.00 medchal
104 ganesh sr.developer 5000.00 balnagar
105 sai sr.developer 6000.00 umanagar
106 narendra TL 7000.00 umanagar
107 nagaraj testing 8000.00 ramnagar
108 ram testing 9000.00 ramnagar
109 janardhan testing 10000.00 ramnagar
110 lakshminarayana trainee 2000.00 yusufguda
but what i am expecting is by using Pivot concept in sql server 2005
empid 101 102 103 104 .......
empname sandeep susheel santhosh ganesh .......
designation trainee developer developer sr.developer .......
salary 2000 3000 4000 5000 .. .....
address begumpet mkonda medchal balanagar .......
please help how to do.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

The most important question: Are you using SQL Server 2005 (as indicated in the header) or SS2000 (as per the forum you posted in)? Makes a huge difference...

You might also search this site for the keyword "TRANSPOSE". There are several solutions available.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004683 More actions · Answer 2

Hi Sandeep and welcome aboard!

It's good that you posted data for an example but most people like to test their code before posting it as an answer. The problem with your data is it's not "readily consumable" data and a lot of people won't take the time to convert it. With that in mind, please take a look at the article at first link in my signature line at the bottom of this post... you'll get much better and quicker answers if you format your data example that way along with a relevant CREATE TABLE statement.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)