Combine similar multiple rows into one

Question

I have a table with data as mentioned below.

Name | Type | Data     | Time
DN60 | LTE  | CPU Load | 2/14/2016 7:00
DN60 | LTE  | CPU Load | 2/14/2016 8:00
DN60 | LTE  | CPU Load | 2/14/2016 9:00

I want the final output to be of one row like below. Since the date will be always for current day, the date value can be ignored in the output.

Name | Type | Data     | Time
DN60 | LTE  | CPU Load | 07:00,08:00,09:00

How can I accomplish this with a simple SQL statement, if at all. I am using SQL Server 2008.

Check this out: http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string You'll need to trim the date off that `Time` field first — Nick, Feb 14 '16 at 06:58

Pரதீப் · Answer 1 · 2016-02-14T08:10:34.900

Here is one way using XML PATH trick

;with cte as
(
SELECT *
FROM   ( VALUES ('DN60','LTE','CPU Load',Cast('2/14/2016 7:00' AS DATETIME)),
                ('DN60','LTE','CPU Load',Cast('2/14/2016 8:00' AS DATETIME)),
                ('DN60','LTE','CPU Load',Cast('2/14/2016 9:00' AS DATETIME)) ) cs (NAME, Type, Data, Time) 
)
SELECT DISTINCT a.NAME,
                a.Type,
                a.Data,
                LEFT(cs.time, Len(cs.time) - 1) AS time
FROM   cte a
       CROSS APPLY (SELECT CONVERT(VARCHAR(20), Time, 8) + ','
                    FROM   cte B
                    WHERE  a.NAME = b.NAME
                           AND a.Type = b.Type
                           AND a.Data = b.Data
                    FOR XML PATH('')) cs (time)

Result:

╔══════╦══════╦══════════╦════════════════════════════╗
║ NAME ║ Type ║   Data   ║            time            ║
╠══════╬══════╬══════════╬════════════════════════════╣
║ DN60 ║ LTE  ║ CPU Load ║ 07:00:00,08:00:00,09:00:00 ║
╚══════╩══════╩══════════╩════════════════════════════╝

SQLFIDDLE DEMO

This is the correct answer for SQL Server. Unfortunately it is this verbose/complex while many other DBMS's do this better and with a simple function. — Nick, Feb 14 '16 at 08:38

Combine similar multiple rows into one

1 Answers1