如何在sql中将列值连接成单个文本？ [英] how to concatenate column values into single text in sql?

问题描述

我有一张桌子



如前：



代码日期

4779 15-08-2015

4779 20-08-2015

1294 15-08-2015

6429 15-08-2015

5268 10-08-2015

5268 11-08-2015

5268 12-08-2015





i需要数据如



empcode日期



4779 15,20

1294 15

6429 15

5268 10,11,12



empcode 4779在一个单元格中有nof天...请告诉我这非常紧急..

i have one table

like ex:

empcode date
4779 15-08-2015
4779 20-08-2015
1294 15-08-2015
6429 15-08-2015
5268 10-08-2015
5268 11-08-2015
5268 12-08-2015


i want data like

empcode date

4779 15,20
1294 15
6429 15
5268 10,11,12

empcode 4779 having nof days in one cell...pls tell me this very urgent..

推荐答案

如果你想要的日子是在一个列中你可以尝试类似下面的内容

If you want the days to be within a single column you could try something like the following

SELECT a.empcode,
       STUFF((SELECT ',' + RTRIM(datepart(day, datecol) ) 
              FROM mytable b 
              WHERE b.empcode = a.empcode 
              FOR XML PATH('')), 1, 1, '') AS dates
FROM (SELECT DISTINCT EmpCode FROM mytable) a

使用Pivoting。这是一个例子。



在SQL查询中使用Pivot的简单方法 [ ^ ]

Use Pivoting. Here is an example.

Simple Way To Use Pivot In SQL Query[^]

使用东西或 Coalesce 获得您想要的相同输出。



请尝试以下查询使用STUFF。

Hi,

Use Stuff or Coalesce to get the same output that you want.

Please try with below query using STUFF.

SELECT DISTINCT empcode,
  STUFF((SELECT distinct ',' + Cast(Day([Date]) AS varchar)
         FROM [Table] p1
         WHERE p.empcode= p1.empcode
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'') as DateConcate
FROM [Table] p;

如果您对此有任何疑虑或疑问或需要更多信息，请与我们联系。这个。



谢谢

Please let me know if you have any concern or query on this or if you need more information on this.

Thanks

这篇关于如何在sql中将列值连接成单个文本？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！