>>>I have a table that looks like this
>>>
>>>Location ServiceDay
>>>
>>>Farmingdale Monday
>>>Farmingdale Tuesday
>>>Bethpage Monday
>>>Bethpage Saturday
>>>
>>>I'd like to have a query that gives me one row for each location like this
>>>
>>>Location Sunday Monday Tuesday Wednesday Thursday Friday Saturday
>>>Farmingdale Yes Yes
>>>Bethpage Yes Yes
>>>
>>>
>>>I want yes or space to show in column relating to the service day
>>>Any ideas?
>>
>>Look up PIVOT operator in SQL Server. It is very easy.
>
>I did and can't make sense of it.
>They seem to want to aggregate.
Select * from (select Location, ServiceDay, 'Yes' as [Service] from ServicesPerformed) X
PIVOT (max(Service) for ServiceDay IN ([Monday], [Tuesday], etc.)) pvt
If you don't like NULL you would need
select Location, COALESCE([Monday], '') as Monday, etc.
from ...
If it's not broken, fix it until it is.
My Blog