将相似的多行合并为一行

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.

如果需要的话，我如何使用一个简单的SQL语句来实现这一点呢?我正在使用SQL Server 2008。

1 个解决方案

#1

Here is one way using XML PATH trick

这里有一种使用XML路径技巧的方法

;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
• SQLFIDDLE演示