SQL PIVOT ON Across Multiple Tables

Question

I am trying to do a PIVOT (am running SQL Server 2008) across multiple tables and with no aggregate function involved. I have to be honest I'm a little out of my depth here and am struggling to define the problem so figure I should just jump in and show you my stuff (oooeeer), firstly I have three tables:

CHARTER_vessels
===============

vesselID    vesselName
--------    ----------
1           The Titanic
2           The Pinafore
3           The Black Pearl


CHARTER_rateDateRange
=====================

rateDateRangeID     rateDateRangeName
---------------     -----------------
1                   Spring 2012
2                   Summer 2012
3                   Fall 2012


CHARTER_rates
=============

vesselID     rateDateRangeID      rateCost
--------     ---------------      --------
1            1                    434
1            2                    445
1            3                    231
2            1                    675
2            2                    545
2            3                    768
3            1                    543
3            2                    654
3            3                    658

And the output I'm trying to achieve is that the rates for each boat appear in the column for each season, like this:

vesselName         Spring 2012     Summer 2012     Fall 2012
----------         -----------     -----------     ---------
The Titanic        434             445             231
The Pinafore       675             545             768
The Black Pearl    543             654             658

Obviously I would like to be able to sort the result set by the different columns if possible!

JStead JStead · Accepted Answer · 2012-01-13T03:32:39

The below makes the assumption of uniqueness of vessel and date range. If this isn't true and you don't want to aggregate a pivot is not for you. The <aggregate>(rateCost) is a requirement to use a SQL Server pivot. There needs to be a mechanism for SQL Server to decide what to return if a vessel has multiple of the same daterange. If this doesn't occur the aggregate is really meaningless. The other option would be a series of self joins. Let me know if you need to see the self join solution.

SELECT src.vesselName,pvt.[Spring 2012], pvt.[Summer 2012], pvt.[Fall 2012]
FROM
(select vesselName, rateCost, rateDateRangeName 
from CHARTER_rateDateRange crd
inner join CHARTER_rates cr on cr.rateDateRangeID = crd.rateDateRangeID
inner join CHARTER_vessels cv on cv.vesselID   = crd.vesselID) AS src
PIVOT
(
max(rateCost)
FOR rateDateRangeName IN ([Spring 2012], [Summer 2012], [Fall 2012])
) AS pvt;

Ah why not in case someone else runs across this is the self join solution. Caution not at all optimized.

with joinMe as (
select vesselName, rateCost, rateDateRangeName 
from CHARTER_rateDateRange crd
inner join CHARTER_rates cr on cr.rateDateRangeID = crd.rateDateRangeID
inner join CHARTER_vessels cv on cv.vesselID   = crd.vesselID
)

select a.vesselName,a.rateCost as 'Spring 2012',b.rateCost as 'Summer 2012',c.rateCost as 'Fall 2012'
from joinMe a
inner join joinMe b on b.vesselName= a.vesselName
                   and b.rateDateRangeName = 'Summer 2012'
inner join joinMe c on c.cesselName = a.vesselName
                    and c.rateDateRangeName = 'Fall 2012'
where a.rateDateRangeName = 'Spring 2012'

Due to the size limit I will write a query response for you here. What does the following return for you anything with a count greater than 1?

select vesselName, rateDateRangeName,count(rateCost)
    from CHARTER_rateDateRange crd
    inner join CHARTER_rates cr on cr.rateDateRangeID = crd.rateDateRangeID
    inner join CHARTER_vessels cv on cv.vesselID   = cr.vesselID
group by vesselName,rateDateRangeName 
order by count(rateCost) desc

SQL PIVOT ON Across Multiple Tables

1 Answers