>>>>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,
>>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 >>>>
>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]) pvtThis one worked.