Query with problem in the group by in Sql server

3080 views sql
-2

So I have this query that works in MySql

SELECT r.*, c.*
From Rsv r
Inner join Clts c ON r.ClientID = c.ClientID
left join Mes m ON m.MesaID = r.MesaID 
WHERE RHS IS NULL AND RC = 0
Group by r.ClientID;

I want this to work in Sql Server and I know that in Sql server when you use the group by, the elements in the select need to be either in the group by or need to have an aggregate function. But I want to select more elements and I don't think I need aggregate funtions on them because it doesn't matter which one it will retrieve the information from since only the MesaNum field is going to be different. How can I achieve this?

EDIT:

Rsv Table:

RsvID|MesaID|ClientID|RsvTime|RsvDate     |RHS   |RC
 1   | 1    | 1      | 8:00  | 2018-09-17 | null | 0
 2   | 2    | 1      | 8:00  | 2018-09-17 | null | 0
 3   | 3    | 2      | 9:00  | 2018-09-17 | null | 0

Desired Result:

RsvID|MesaID|ClientID|RsvTime|RsvDate     |RHS   |RC
1    | 1,2  | 1      | 8:00  | 2018-09-17 | null | 0
3    | 3    | 2      | 9:00  | 2018-09-17 | null | 0

(Sorry, couldn't figure out how to do tables here)

answered question

Please provide sample data and desired results. Using SELECT * with GROUP BY simply makes no sense. The query should be using aggregation functions.

1 Answer

1

You can try to use STUFF function to do groupby_concat, then make row number by ClientID get rn = 1

SELECT * FROM (
    SELECT RsvID,ClientID,RsvTime,RsvDate,RHS,RC,
           STUFF((
              SELECT ',' + CAST(tt.MesaID AS VARCHAR(5))
              FROM Rsv tt
              WHERE tt.ClientID = t1.ClientID
              FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') MesaID,
           ROW_NUMBER() OVER(PARTITION BY ClientID ORDER BY RsvID) rn
    FROM Rsv t1
) t1
where rn = 1

sqlfiddle

posted this

Have an answer?

JD

Please login first before posting an answer.