pivot query - 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 query

Author

Topic

mrm23
Posting Yak Master

198 Posts

Posted - 2010-05-24 : 02:20:49

Hi All,

I have a query where PIVOT is used often. So i tried to understand it by working it on some sample data...

here is my sales table:
Product Year
Car 1990
Bike 1990
Truck 1990
Bus 1992
Car 1992

now if i have to get data as

1990 Car Bike Truck
1992 Car Bus

how should i write query?

i tried wirting this, but i am getting error in the first line itself.

select year,['Car','Bike','Truck','Scooter','Bus']
from (select product,year from sales) as source
pivot
(count(year )
for product in (['Car','Bike','Truck','Scooter','Bus'])
) as pvt

could you please help?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-24 : 03:11:05

Just try the below statement and tell me whether you were looking for this or your requirement is something different..

Declare @Sales table
(Product varchar(25),
SalesYear int)

Insert into @Sales
Select 'Car' ,1990 union
Select 'Bike' ,1990 union
Select 'Truck' ,1990 union
Select 'Bus', 1992 union
Select 'Car' ,1992

select SalesYear,[Car],[Bike],[Truck],[Scooter],[Bus]
from (select product,SalesYear, SalesYear as SalesYear1 from @Sales) as source
pivot
(count(SalesYear1 )
for product in ([Car],[Bike],[Truck],[Scooter],[Bus])
) as pvt

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

mrm23
Posting Yak Master

198 Posts

Posted - 2010-05-24 : 03:14:26

i got this error:

Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-24 : 04:31:41

quote:
Originally posted by mrm23

i got this error:

Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

Are you executing this query in Sql Server management studio or any other application ?

mrm23
Posting Yak Master

198 Posts

Posted - 2010-05-24 : 04:40:36

In Management studio itself... i got the solution just now...
it is as below:
declare @table table (Product varchar(20), [year] int)
insert into @table (Product ,[Year])
select 'Car', 1990 union all
select 'Bike', 1990 union all
select 'Truck', 1990 union all
select 'Bus', 1992 union all
select 'Car', 1992

select Product ,[Year] from @table

select [Year],[Car] Prod1,[Bike] Prod2,[Truck] Prod13 ,[Scooter] Prod4,[Bus] Prod5
from(select product,[year] from @table ) source
pivot (MAX([product]) for [product] in ([Car],[Bike],[Truck],[Scooter],[Bus]) ) pvt

***************************************************************************************************

However, i would like to know abt the error ....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources