如何在SQL中填充日期和时间 [英] how to stuff date and time in SQL

问题描述

我在用SQL服务器填充同一项目的日期和时间时遇到麻烦.以下是三个简单的记录.

i am having trouble stuffing the date and time for the same item in SQL sever. Below are three simple records.

Date_Time                ID
9/6/2018 5:36:30 PM      12345
9/6/2018 6:19:02 PM      12345
9/7/2018 4:15:55 AM      12345

我希望我的结果在第一行，并且将Date_Time列填充为逗号，然后用逗号(，).

I would like my result to be in 1 row and the Date_Time column got stuffed followed by a comma (,).

Date_Time                                                       ID
9/6/2018 5:36:30 PM, 9/6/2018 6:19:02 PM, 9/7/2018 4:15:55 AM   12345

有人可以指出正确的方向/链接或简单的查询来解决此问题吗?

Would someone please point me to the right direction/link or a simple query to resolve the issue?

谢谢， XH.

推荐答案

如果您使用的是SQL Server 2017或更高版本，则可以使用string_agg，如下所示:

If you are using sql server 2017 or above you can use string_agg as below:

select id, string_agg(convert(varchar,DATE_Time, 22), ', ')
    from #YourTable 
    group by id

如果它在sql server 2017以下，则可以使用以下查询:

If it is below sql server 2017 you can use below query:

select id, stuff ((
    select ', ' + convert(nvarchar, date_time, 22) from #Table2 where id = t.id
    for xml path('')
    ),1,2,'') as dates
from #Table2 t
group by id

这篇关于如何在SQL中填充日期和时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！