Pivot-related query

I have a couple of tables (see below) that tell me what days a particular
customer depot (LocationID) is scheduled to have deliveries on (DeliveryDays
table), and what days the Courier picks up for these deliveries
(CarrierCollections table)
I want to create a query (a view actually) that tells me for each depot,
which carrier collection days are possible/appropriate - this is to help the
people who are preparing the goods for despatch. The complication is that I
want one row for each Depot, eg:
LocationID, Mon, Tues, Weds, Thurs, Fri
8, null, 1, null, 1, null
9, null, null, null, 1, null
Pivots are fiddly enough as it is, but combining records in this way is
beyond me as yet. Has anyone got any ideas or solutions?
I can achieve the result I want by running one query within another (in my
ASP application) but this would be very inefficient and would significantly
slow down this page, so I would like to make SQL do the hard work so I only
need to do one query in my ASP app.
Thanks in advance...
Chris
Tables & Data:
CREATE TABLE [dbo].[CarrierCollections] (
[CarrierID] [int] NOT NULL ,
[CollectionDay] [tinyint] NOT NULL ,
[DeliveryDay] [tinyint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DeliveryDays] (
[LocationID] [int] NOT NULL ,
[DeliveryDay] [tinyint] NOT NULL
) ON [PRIMARY]
GO
Insert into DeliveryDays Values (8, 3)
Insert into DeliveryDays Values (8, 5)
Insert into DeliveryDays Values (9, 2)
Insert into DeliveryDays Values (9, 6)
Insert into CarrierCollections Values (119, 3, 4)
Insert into CarrierCollections Values (119, 3, 5)
Insert into CarrierCollections Values (119, 5, 2)
Insert into CarrierCollections Values (119, 5, 3)
Insert into CarrierCollections Values (119, 5, 6)
Note: 1 = Sunday, 7 = Saturday
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]Something like:
SELECT d1.LocationID,
MAX( CASE c1.DeliveryDay WHEN 1 THEN 1 END ) AS "Sunday",
MAX( CASE c1.DeliveryDay WHEN 2 THEN 1 END ) AS "Monday",
MAX( CASE c1.DeliveryDay WHEN 3 THEN 1 END ) AS "Tuesday",
MAX( CASE c1.DeliveryDay WHEN 4 THEN 1 END ) AS "Wednesday",
MAX( CASE c1.DeliveryDay WHEN 5 THEN 1 END ) AS "Thursday",
MAX( CASE c1.DeliveryDay WHEN 6 THEN 1 END ) AS "Friday",
MAX( CASE c1.DeliveryDay WHEN 7 THEN 1 END ) AS "Saturday"
FROM CarrierCollections c1
INNER JOIN DeliveryDays d1
ON c1.DeliveryDay = d1.DeliveryDay
GROUP BY d1.LocationID ;
Anith|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uMs6WifEGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Something like:
> SELECT d1.LocationID,
> MAX( CASE c1.DeliveryDay WHEN 1 THEN 1 END ) AS "Sunday",
> MAX( CASE c1.DeliveryDay WHEN 2 THEN 1 END ) AS "Monday",
> MAX( CASE c1.DeliveryDay WHEN 3 THEN 1 END ) AS "Tuesday",
> MAX( CASE c1.DeliveryDay WHEN 4 THEN 1 END ) AS "Wednesday",
> MAX( CASE c1.DeliveryDay WHEN 5 THEN 1 END ) AS "Thursday",
> MAX( CASE c1.DeliveryDay WHEN 6 THEN 1 END ) AS "Friday",
> MAX( CASE c1.DeliveryDay WHEN 7 THEN 1 END ) AS "Saturday"
> FROM CarrierCollections c1
> INNER JOIN DeliveryDays d1
> ON c1.DeliveryDay = d1.DeliveryDay
> GROUP BY d1.LocationID ;
Bingo!
Damn! I almost got this far but missed out the MAX() function. Seems obvious
in retrospect.
Thanks very much Anith.
Chris