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?

If you are seriously using SQL Server 2008 its time to upgrade! — Dale K
– Dale K, Commented Mar 19, 2021 at 3:38
@DaleK I know, but we have some 2008 servers) You know how difficult to upgrade old servers) — Alexander Jamkin
– Alexander Jamkin, Commented Mar 19, 2021 at 3:40

ASH · Accepted Answer · 2021-03-19 04:32:32Z

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:

answered Mar 19, 2021 at 4:32

ASH

20.5k28 gold badges116 silver badges247 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to concatenate values from multiple columns in a single row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related