Pivoting Resultset of a query

[TonyKhela]

Hi,

I need to know how to pivot (This may not be the correct term) the resultset of a query.

EG. When I run:
use pubs
select au_lname, au_fname, city
from authors where au_lname in ('white','green')

I get the followig resultset:
au_lname au_fname city
Green Marjorie Oakland
White Johnson Menlo Park

But I want it in the form:
au_lname Green White
au_fname Marjorie Johnson
city Oakland Menlo Park

ie. I need to see the rows as columns and coumns as rows.
Can anyone assist please.

Thanks

TK

 

[LNBruno]

Take a look at this as a start:

http://www.tek-tips.com/viewthread.cfm?qid=1391408&page=1

< M!ke >
Your right to an opinion does not obligate me to take you seriously.
- Winston Churchill

 

[TonyKhela]

No, didn't really help.
I wanted a simple sproc or query to turn columns into rows and rows in to columns.
Would be grateful for any hekp.
thanks
TK

 

[ca8msm]

You should have been able to use the method in the thread that Mike linked to as it does do what you are asking. Another demonstration of this PIVOT method can be found here:

http://technet.microsoft.com/en-us/library/ms177410.aspx

____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[ESquared]

Why do you want to pivot like this? If it's just a presentation issue, do it in the presentation layer, on the client.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]