Random_Display_Name Random_Display_Name - 2 months ago 9
SQL Question

SQl Server multiple rows group by

Lets suppose i got those 2 tables :

ID Time UserId
1 08:00 55
2 08:00 22
3 08:00 04
4 09:00 17
5 11:00 11
...etc...

UserId Name
01 Brian
02 Francis
03 David
...etc...


My wanted results are:

Hours(distinct) Name
08:00 Franck,Michelle,Damian
09:00 Indiana, Robert


In fact , i want all the distinct hours to be listed in the first column and all the names of users listed as one row. I tried this query :


SELECT distinct convert (varchar(25),Time ,120) ,test = STUFF ((
SELECT ',' + Name From T2 WHERE T1.UserId=T2.Id FOR XML PATH('')),
1,1,'') FROM T1 group BY Time


the query works without the group by but it trigger an error with it " Column 'UserId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

Any idea ?

Answer

XML aggregation must only depend on columns listed in GROUP BY, time in your case

SELECT distinct convert (varchar(25),Time ,120) 
 ,test = STUFF (( 
    SELECT ',' + Name 
    FROM T2 
    WHERE T2.UserId IN (
         SELECT T3.UserID 
         FROM T1 AS T3
         WHERE T3.time=T0.time)
   FOR XML PATH('')), 1,1,'') 
FROM T1 as T0 
GROUP BY Time
Comments