How to concatenate values from multiple columns in a single row

Question

I have this table, I need to concatenate the same values from serveral columns in one row.

RowId  ServerName  MyApplicationName   UserName       StartTime     LastTime     UsersCount
 1        Prod          MyProd           User1        2021/03/09     2021/03/09       10
 2        DEV1          MyApp1           User1        2021/03/12     2021/03/13       3
 3        DEV1          MyApp1           User1        2021/03/14     2021/03/15       3
 4        DEV1          MyApp1           User1        2021/03/16     2021/03/17       4
 5        DEV1          MyApp1           User1        2021/03/18     2021/03/19       5

I need following result:

RowId  ServerName  MyApplicationName   UserName       StartTime     LastTime     UsersCount
 1        Prod          MyProd           User1        2021/03/09     2021/03/09       10
 2        DEV1          MyApp1           User1        2021/03/12     2021/03/19       15

What I tried:

SELECT
       RowId,
       STUFF(
                        (SELECT ',' + ServerName
                        FROM tbl t1
                        WHERE t2.ServerName = t1.ServerName
                        FOR XML PATH(''))
                        , 1, 1, '')
                ) As ServerName,
       STUFF(
                        (SELECT ',' + MyApplicationName
                        FROM tbl t1
                        WHERE t2.MyApplicationName = t1.MyApplicationName
                        FOR XML PATH(''))
                        , 1, 1, '')
                ) As MyApplicationName,
       STUFF(
                        (SELECT ',' + UserName
                        FROM tbl t1
                        WHERE t2.UserName = t1.UserName
                        FOR XML PATH(''))
                        , 1, 1, '')
                ) As UserName,
       MIN(StartTime) AS StartTime,
       MAX(LastTime) AS LastTime,
       SUM(UserCount) AS UserCount

GROUP BY ApplicationName

But it doesn't works. How do it correctly and what I did wrong?

Answer 1

This is just a min, max, sum exercise, is it not,

--DROP TABLE t 

CREATE TABLE t  (
    ServerName    varchar(100),
    myapplicationname   varchar(100),
    username    varchar(100),
    starttime   varchar(100),
    endtime    varchar(100),
    usercount int)

INSERT INTO  t ( ServerName, myapplicationname, username, starttime, endtime, usercount)
VALUES
('Prod','myprod','user1','2021/03/09','2021/03/09', 10),
('dev1','MyApp1','user1','2021/03/12','2021/03/13', 3),
('dev1','MyApp1','user1','2021/03/14','2021/03/15', 3),
('dev1','MyApp1','user1','2021/03/16','2021/03/17', 5)

select *
from t

select ServerName, myapplicationname, username, min(starttime), max(endtime), sum(usercount)
from t
group by ServerName, myapplicationname, username

Result: