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

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:

Collectives™ on Stack Overflow

How to concatenate values from multiple columns in a single row

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related