>>>This distributor wants a listing of locations to be delivered by day
>>>
>>>

>>>Sunday  Monday Tuesday   etc
>>>loc a       loc b       loc d
>>>loc j        loc k        etc           etc
>>>
>>>

>>>the table contains the daynumber Sunday =1, and the location,
>>>
>>>Can it be done in one query?
>>
>>Naomi's answer is likely to suffice. But just curious, can you post the DDL for the table(s) involved?
>
>I tried Naomi's answer. I got blank values in some of the column/row cells and couldn't figure out how to prevent that.
>
>
>Here is the structure:
>The important columns are storecode( location) and daynumber (1 = Sunday)
>
>

>CREATE TABLE [dbo].[dropschedule](
>	[storecode] [char](11) NULL,
>	[daynumber] [decimal](1, 0) NULL,
>	[num_drops] [decimal](2, 0) NULL,
>	[route] [char](3) NULL,
>	[driver_1] [char](2) NULL,
>	[condition] [bit] NULL,
>	[cfrom] [datetime] NULL,
>	[cthru] [datetime] NULL,
>	[commission] [decimal](3, 0) NULL,
>	[each_cycle] [bit] NULL,
>	[t_when] [decimal](2, 0) NULL,
>	[schday] [char](3) NULL,
>	[adduser] [char](4) NULL,
>	[adddate] [datetime] NULL,
>	[addtime] [char](8) NULL,
>	[lckstat] [char](1) NULL,
>	[lckuser] [char](4) NULL,
>	[lckdate] [datetime] NULL,
>	[lcktime] [char](8) NULL,
>	[adjustment] [money] NULL,
>	[commtype] [char](1) NULL
>) ON [PRIMARY]
>
>GO
>

>
>I'm half way through doing this the old-fashioned way with datatables- aka filling up cursors- but I'd prefer to have a query that did it.

What is the query you tried? By the way, decimal (1,0) has no sense. You should have used tinyint instead. Also, why all columns are nullable? What is the primary key for the table? Can you have NULL in StoreCode and in DayNumber columns?

The query I provided for you should have worked. You would get empty values in cases where you don't have the same number of StoreCode per DayNumber.

E.g.

select ISNULL([1],'') as Sunday, ISNULL([2],'') as Monday, etc.
 from (select DayNumber, StoreCode, dense_rank() over (partition by DayNumber order by StoreCode) as Rnk from dbo.DropSchedule) src PIVOT (max(StoreCode) for DayNumber IN ([1],[2], [3],[4],[5],[6],[7]) pvt