>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?
Sure, look into PIVOT syntax in SQL Server.
You may find this blog post interesting as well
http://blogs.lessthandot.com/index.php/datamgmt/datadesign/understanding-sql-server-2000-pivot/E.g.
declare @t table (id int identity (1,1), location varchar(10), DayNumber tinyint)
insert into @t (location, DayNumber)
values ('Loc1', 1), ('Loc2', 1), ('Loc3', 2), ('Loc4',2), ('Loc5', 1), ('Loc6', 1)
select ISNULL(min(case when DayNumber = 1 then Location end),'') as Sunday,
ISNULL(min(case when DayNumber = 2 then Location end),'') as Tuesday
from (select *, row_number() over (partition by DayNumber order by Location) as Rn
from @t) X
GROUP BY Rn
If it's not broken, fix it until it is.
My Blog