How can I group table after inner join?

Question

I am using SQL Server 2012.

I have this table called InspectionReviews:

|Id  | SiteId| IsNormal|     DateReview            | ObjectId |FrequencyId|InspectItemId |
|3379|      5|  True   |    2016-09-08 00:00:00.000|    1019  |     1     |     16       |
|3380|      5|  True   |    2016-09-08 00:00:00.000|    1019  |     1     |     20       |
|3381|      5|  False  |    2016-09-08 00:00:00.000|    1020  |     1     |     16       |
|3382|      5|  True   |    2016-09-08 00:00:00.000|    1020  |     1     |     54       |

IsNormal is a bit column.

And this table called DamageEvents:

| Id | ExternalId | Subject | CMT           |    
| 1  |   3379     | damage5 | some comment7 |
| 2  |   3380     | damage3 | some comment3 | 
| 3  |   3382     | damage4 | some comment5 |
| 4  |   3381     | damage1 | some comment4 |

The ExternalId column in DamageEvents table is a foreign key.

I wrote an inner join between the two tables:

SELECT  
    InspectionReviews.Id, InspectionReviews.SiteId,
    InspectionReviews.IsNormal, InspectionReviews.DateReview,
    InspectionReviews.ObjectId, InspectionReviews.FrequencyId, 
    InspectionReviews.InspectItemId, 
    DamageEvents.ExternalId, DamageEvents.Subject, DamageEvents.CMT,
FROM    
    InspectionReviews 
INNER JOIN
    DamageEvents ON InspectionReviews.Id = DamageEvents.ExternalId

The result I get:

|Id  | SiteId| IsNormal|     DateReview            | ObjectId |FrequencyId|InspectItemId | ExternalId | Subject |     CMT       |
|3379|  5    |  True   |    2016-09-08 00:00:00.000|    1019  |     1     |     16       |    3379    | damage5 | some comment7 |
|3380|  5    |  True   |    2016-09-08 00:00:00.000|    1019  |     1     |     20       |    3380    | damage3 | some comment3 |
|3381|  5    |  False  |    2016-09-08 00:00:00.000|    1020  |     1     |     16       |    3381    | damage4 | some comment5 |
|3382|  5    |  True   |    2016-09-08 00:00:00.000|    1020  |     1     |     54       |    3382    | damage1 | some comment4 |

After I implement inner join I need to make group by ObjectId. And here is desired result:

|    Id   | SiteId| IsNormal|     DateReview           | ObjectId |FrequencyId|InspectItemId | Subject |     CMT                     |
|3379,3380|  5    |  True   |   2016-09-08 00:00:00.000|    1019  |     1     |     16,20    | damage5 | some comment7,some comment3 |
|3381,3382|  5    |  False  |   2016-09-08 00:00:00.000|    1020  |     1     |     16,54    | damage4 | some comment5,some comment4 |

I need to group the table above after (inner join) by ObjectId, if at least one row has IsNormal false in grouped table it have to be False.

How can I implement it?

Answer 1

You can use query like this:

; with cte as (
SELECT  InspectionReviews.Id,
        InspectionReviews.SiteId,
        InspectionReviews.IsNormal,
        InspectionReviews.DateReview,
        InspectionReviews.ObjectId,                 
        InspectionReviews.FrequencyId, 
        InspectionReviews.InpectItemId, 
        DamageEvents.ExternalId, 
        DamageEvents.Subject,
        DamageEvents.CMT

FROM    #inspectionreviews InspectionReviews INNER JOIN
        #damageevents DamageEvents ON InspectionReviews.Id = DamageEvents.ExternalId
)
select objectid, min(convert(int,IsNormal)) as IsNormal , stuff ((
select ',' + convert(varchar(5),Id) from cte where objectid = t.objectid 
for xml path('')
),1,1,'') as Id
, stuff ((
select ',' + convert(varchar(5),inpectitemid) from cte where objectid = t.objectid 
for xml path('')
),1,1,'') as InspectItemId
, stuff ((
select ',' + subject from cte where objectid = t.objectid 
for xml path('')
),1,1,'') as Subject
, stuff ((
select ',' + CMT from cte where objectid = t.objectid 
for xml path('')
),1,1,'') as CMT,
max(frequencyid) as FrequencyId,
max(SiteId) as SiteId
from cte t group by objectid

Answer 2

May this help-

select
    ObjectId,
    Id = Stuff( (select ',' + convert(varchar(100), Id) 
                 from InspectionReviews     
                  where ObjectId = ir.ObjectId for xml path('')), 1, 1, ''),
    IsNormal = Min(convert(int, IsNormal))
from
    InspectionReviews ir
group by
    ObjectId