sql 2008 PIVOT

Thanks for any help

What do you want to pivot on and what is the aggregate?  e.g.
   PIVOT(SUM(value_column) FOR key_column IN ([val1],[val2],[val3]))p

Some ideas on the data and the expected result makes these thing easier to answer.

Hello,
if I run the code as is,
I get
PlanName              FromZone                ToZone      Price
Montreal1             M-CENTREVILLE                M-H9P      30.80
Montreal2             M-CENTREVILLE                M-H9P      55.00
Montreal3             M-CENTREVILLE                M-H9P      55.00
Montreal4             M-CENTREVILLE                M-H9P      55.00


I'm trying to get it this way
FromZone              ToZone      Montreal1   Montreal2  Montreal3  Montreal4
M-CENTREVILLE      M-H9P      30.80           55.00             55.00      55.00


thanks,

Firstly ... your original query using joins ...

Select PricePlans.Description AS [PlanName],
       z1.Description AS [FromZone], 
       z2.Description  AS [ToZone],
       Price 
From priceszone 
inner join PricePlans on PricePlans.PricePlanID = PricesZone.PricePlanID
inner join Zones z1 where z1.ZoneID = ZoneFromID
inner join Zones z2 where z2.ZoneID = ZoneToID
where PricePlanID in (1529,1528,1527,1526) AND (ZONEFROMID = 105 OR ZONETOID = 105)
ORDER BY PricePlans.Description, FromZone, ToZone

Select allOpen in new window

Into a PIVOT

SELECT [FromZone], [ToZone], [Montreal1],[Montreal2],[Montreal3],[Montreal4]
FROM (Select z1.Description AS [FromZone], 
             z2.Description  AS [ToZone],
             PricePlans.Description AS [PlanName],
             Price 
      From priceszone 
      inner join PricePlans on PricePlans.PricePlanID = PricesZone.PricePlanID
      inner join Zones z1 where z1.ZoneID = ZoneFromID
      inner join Zones z2 where z2.ZoneID = ZoneToID
      where PricePlanID in (1529,1528,1527,1526) AND (ZONEFROMID = 105 OR ZONETOID = 105))v
PIVOT(SUM(Price) FOR PlanName IN ([Montreal1],[Montreal2],[Montreal3],[Montreal4])pvt

Select allOpen in new window

HI lwadwell,
I think I'll be using the join instead,
I get an error

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'where'.

I should have fully qualified the column in the where, change
    where PricePlanID in
to
    where PricesZone.PricePlanID in

Same error
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'where'.

Which query?  Are you running them as I entered them or have you modified them?  The error has "Line 7" which is a JOIN.  
As I do not have your database, tables and data - I cannot run the SQL to check syntax etc.

Select PricePlans.Description AS [PlanName],
       z1.Description AS [FromZone],
       z2.Description  AS [ToZone],
       Price
From priceszone
inner join PricePlans on PricePlans.PricePlanID = PricesZone.PricePlanID
inner join Zones z1 where z1.ZoneID = ZoneFromID
inner join Zones z2 where z2.ZoneID = ZoneToID
where PricesZone.PricePlanID in  (1529,1528,1527,1526) AND (ZONEFROMID = 105 OR ZONETOID = 105)
ORDER BY PricePlans.Description, FromZone, ToZone

Bother ... didn't change the WHERE to ON when converted from inline subquery to join.

Select PricePlans.Description AS [PlanName],
       z1.Description AS [FromZone],
       z2.Description  AS [ToZone],
       Price
From priceszone
inner join PricePlans on PricePlans.PricePlanID = PricesZone.PricePlanID
inner join Zones z1 ON z1.ZoneID = ZoneFromID
inner join Zones z2 ON z2.ZoneID = ZoneToID
where PricesZone.PricePlanID in  (1529,1528,1527,1526) AND (ZONEFROMID = 105 OR ZONETOID = 105)
ORDER BY PricePlans.Description, FromZone, ToZone

I appreciate your help,
I thought the join would have given me the format I wanted, but it gives me my original results. (same column pricing)

if I use the pivot, (I get error)
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'where'.

If I do the  same changes you did for the join,
add ON
REPLACE   where PricesZone.PricePlanID in

i get error
Msg 4145, Level 15, State 1, Line 8
An expression of non-boolean type specified in a context where a condition is expected, near 'where'.

SELECT [FromZone], [ToZone], [Montreal1],[Montreal2],[Montreal3],[Montreal4]
FROM (Select z1.Description AS [FromZone],
             z2.Description  AS [ToZone],
             PricePlans.Description AS [PlanName],
             Price
      From priceszone
      inner join PricePlans on PricePlans.PricePlanID = PricesZone.PricePlanID
      inner join Zones ON z1 where z1.ZoneID = ZoneFromID
      inner join Zones ON z2 where z2.ZoneID = ZoneToID
      where PricesZone.PricePlanID in(1529,1528,1527,1526) AND (ZONEFROMID = 105 OR ZONETOID = 105))v
PIVOT(SUM(Price) FOR PlanName IN ([Montreal1],[Montreal2],[Montreal3],[Montreal4])pvt

i think you can do one thing store the result in any temp table and write your pivot query on that it will help you to understand...


Select (select Description from PricePlans where PricePlanID = PricesZone.PricePlanID) AS [PlanName],
(select Description From Zones where ZoneID = ZoneFromID) AS [FromZone],
(select Description From Zones where ZoneID = ZoneToID) AS [ToZone],Price into tempdb.dbo.temp_table_name
From priceszone where PricePlanID in (1529,1528,1527,1526) AND (ZONEFROMID = 105 OR ZONETOID = 105)
ORDER BY (select Description from PricePlans where PricePlanID = PricesZone.PricePlanID) ,FromZone, ToZone

select FromZone,ToZone,Montreal1,Montreal2,Montreal3,Montreal4  from (select [PlanName],[FromZone],[ToZone],Price  from temp_table_name ) v pivot(SUM(Price) FOR PlanName IN ([Montreal1],[Montreal2],[Montreal3],[Montreal4]))pvt

thank you guys,