Avatar of W.E.B
W.E.B
 asked on

sql 2008 PIVOT

Hello,
can you please help me getting this code as a pivot.

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

Any help is appreciated.
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
W.E.B

8/22/2022 - Mon
W.E.B

ASKER
Thanks for any help
Lee Wadwell

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.
W.E.B

ASKER
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,
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Lee Wadwell

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

Open 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

Open in new window

W.E.B

ASKER
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'.
Lee Wadwell

I should have fully qualified the column in the where, change
    where PricePlanID in
to
    where PricesZone.PricePlanID in
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
W.E.B

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

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.
W.E.B

ASKER
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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Lee Wadwell

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
W.E.B

ASKER
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
SOLUTION
Lee Wadwell

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
keyu

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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Brainfeb

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
W.E.B

ASKER
thank you guys,