CODEWITHSUNDEEP
sqlsql-server
Hugo Gaspar
Hugo Gaspar

Reputation: 65

Query with problem in the group by in SQL Server

I have this query that works in MySQL:

SELECT 
    r.*, c.*
FROM 
    Rsv AS r
INNER JOIN 
    Clts AS c ON r.ClientID = c.ClientID
LEFT OUTER 
    Mes AS m ON r.MesaID = m.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 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 functions 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)

Upvotes: 0

Views: 105

Answers (1)

D-Shih
D-Shih

Reputation: 46219

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

Upvotes: 1

Related Questions