Combining multiple rows in TSQL query

Question

I have a table with data like this

Road  Item Response  added_on
1     82   Yes       7/11/16
1     83   Yes       7/11/16
1     84   Yes       7/11/16
2     82   Yes       8/11/16 
2     83   No        8/11/16
2     85   Yes       8/11/16

This reflects an assessment of a road where 'item' is things being assessed. Some items will always be done during an assessment (82, 83) where others are optional (84, 85). I want to return something that combines all of the assessment results for a road/date, returning null if that item was not assessed. And also only returning last month's results. For example

Road  82  83  84  85   added_on
1     Yes Yes Yes      7/11/16
2     Yes No      Yes  8/11/16

I have tried a multiple self joins like this but it's returning nothing.

FROM assess AS A
JOIN assess AS B
ON A.road = B.road AND a.added_on = B.added on
JOIN assess AS C
ON A.road = C.road AND a.added_on = C.added on
JOIN assess AS D
ON A.road = D.road AND a.added_on = D.added on

WHERE A.item = '81'
AND B.item = '82'
AND (C.item = '83' OR C.item IS NULL)
AND (D.item = '84' OR D.item IS NULL)
AND datepart(month,A.added_on) = datepart(month,getdate()) -1

To clarify,

-no road is assessed more than once a day
-each item is only assessed once, and sometimes is NULL i.e. not applicable
-multiple roads are assessed each day
-this table has other assessments but we aren't worried about those.

Any ideas? Using SQL server 2008. Thanks.

Answer 1

I would do this using conditional aggregation:

select road,
       max(case when item = 82 then response end) as response_82,
       max(case when item = 83 then response end) as response_83,
       max(case when item = 84 then response end) as response_84,
       max(case when item = 85 then response end) as response_85,
       added_on
from t
group by road, added_on
order by road;

For the month component, you can add a where clause. One method is:

where year(date_added) * 12 + month(date_added) = year(getdate())*12 + month(getdate()) - 1

Or, you can use logic like this:

where date_added < dateadd(day, 1 - day(getdate()), cast(getdate() as date)) and
      date_added >= dateadd(month, -1, dateadd(day, 1 - day(getdate()), cast(getdate() as date)))

The second looks more complicated but it is sargable, meaning that an index on date_added can be used (if one is available).

Answer 2

Another way of achieving this is less elegant, but uses basic operations if you don't want to use pivot.

Load up test data

create table #assess ( road int, item varchar(10), response varchar(3), added_on date )
insert #assess( road, item, response, added_on )
values 
  (1, '82', 'Yes', '2016-07-11' )
, (1, '83', 'Yes', '2016-07-11' )
, (1, '84', 'Yes', '2016-07-11' )
, (2, '82', 'Yes', '2016-08-11' )
, (2, '83', 'No', '2016-08-11' )
, (2, '85', 'Yes', '2016-08-11' )

Process the data

-- Get every possible `item`
select distinct item into #items from #assess

-- Ensure every road/added_on combination has all possible values of `item`
-- If the combination does not exist in original data, leave `response` as blank
select road, added_on, i.item, cast('' as varchar(3)) as response into #assess2
from #items as i cross join #assess AS A
group by road, added_on, i.item 

update a set response = b.response
from #assess2 a inner join #assess b on A.road = B.road AND a.added_on = B.added_on AND a.item = b.item

-- Join table to itself 4 times - inner join if `item` must exist or left join if `item` is optional
select a.road, a.added_on, a.response as '82', b.response as '83', c.response as '84', d.response as '85'
FROM #assess2 AS A
INNER JOIN #assess2 AS B    ON A.road = B.road AND a.added_on = B.added_on
LEFT JOIN #assess2 AS C     ON A.road = C.road AND a.added_on = C.added_on
LEFT JOIN #assess2 AS D     ON A.road = D.road AND a.added_on = D.added_on

WHERE A.item = '82'
AND B.item = '83'
AND (C.item = '84' OR C.item IS NULL)
AND (D.item = '85' OR D.item IS NULL)
--AND datepart(month,A.added_on) = datepart(month,getdate()) -1 

The resultset is:

road    added_on    82  83  84  85
1       2016-07-11  Yes Yes Yes 
2       2016-08-11  Yes No      Yes

Answer 3

Assuming you need to go Dynamic

Declare @SQL varchar(max) 
Select  @SQL = Stuff((Select Distinct ',' + QuoteName(Item) From YourTable Order By 1 For XML Path('')),1,1,'') 
Select  @SQL = 'Select [Road],' + @SQL + ',[added_on] 
                From YourTable
                Pivot (max(Response) For Item in (' + @SQL + ') ) p'
Exec(@SQL);

Returns

EDIT - The SQL Generated is as follows. (just in case you can't go dynamic)

Select [Road],[82],[83],[84],[85],[added_on]
 From  YourTable
 Pivot (max(Response) For Item in ([82],[83],[84],[85]) ) p