Pivot Question - SQL Server Forums

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)

Pivot Question

Author

Topic

shiyam198
Yak Posting Veteran

94 Posts

Posted - 2010-05-05 : 00:16:14

Hi,

The following SQL stamtement.

select city, customerno from customer

produces the following table -

http://img188.imageshack.us/img188/9445/table1i.png

Now, I want to show the data this way - All the data in the first column should become column headers, and the data in the second column should becomes the data.

If I use the following SQL

select *
from
(
select city,customerno from customer
) as CT
PIVOT
(
avg(customerno) for city in (Aspen,London,NY,Toronto,Vancouver)
) as pvt

It is working with no error. But that is not what i want. I don't want to use a aggregate function. Becasue when i use the avg function, what i get is this -

http://img156.imageshack.us/img156/8859/tanble2.png

I want to show all the data 3 columns for Toronto, 3 for vancouver, etc.

Please let me know how I can achieve that. I googled a lot on this. but could not find answers.

Much appreciated.

Thanks,
Shiyam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-05 : 02:23:26

try this

select *
from
(
select city,customerno,row_number() over (partition by city order by customerno) as seq from customer
) as CT
PIVOT 
(
max(customerno) for city in (Aspen,London,NY,Toronto,Vancouver)
) as pvt

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-07 : 07:19:00

For unknown number of cities, use
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

shiyam198
Yak Posting Veteran

94 Posts

Posted - 2010-05-07 : 13:51:40

Hi,

Thanks Vishak and Madhivanan. I am not sure but somehow unlike other parts in SQL, PIVOT is something i am having difficulty wrapping my head around it.

Can you please provide me a source where i can learn this from basic?

I am not sure why we are doing certain things when we are pivoting.
For e.g.

WHY IS THIS WORKING?

select *
from
(
select city, customerno from customer
) as CT
pivot
(
max(customerno) for city in (Aspen,Toronto)
) as pvt

WHILE THIS DOES NOT WORK

select city, customerno from customer
pivot
(
max(customerno) for city in (Aspen,Toronto)
) as pvt

Because if the outer select in the first SQL statment gives the same result

Thanks guys. Any guidance is much appreciated.

- Shiyam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-07 : 14:03:51

because once you pivot on city based on values the column city dont exist no more. you need to use Aspen,Toronto etc in select

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources