May 24, 2010 at 12:36 am
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?
May 24, 2010 at 12:48 am
Malavika, you have to PIVOT Products for Products in this case. so your code must look like this:
First, lets set up the test environment:
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
Now let's PVIOT Products for Products:
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
Hope this helps you! Tell us back if it helped you!
C'est Pras!
~Edit: Removed unwanted commented code!
May 24, 2010 at 1:24 am
Hi ColdCoffee,
It is working thanks...
but my prob is i am not very clear with this PIVOT.
here u have used max(product)...
now how to decide which aggregate func to use?
and also if you can explain the pivot part it will be helpful
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply