Concatenating multiple dates into a single varchar cell

Question

I have rewritten this question to be more straightforward. I have a SQL query:

SELECT CAST(CASE WHEN DTH.DoubleBookedFlag = 1 THEN DTH.ActivityDate END AS VARCHAR(MAX)) AS DoubleBookedHours
FROM DailyTaskHours DTH
WHERE DTH.DoubleBookedFlag = 1

Here is the result:

enter image description here

Instead of returning as multiple rows, I would like whatever is returned to be one long concatenated string in a single VARCHAR field seperate by a character like |.

How can I achieve this?

Check this [question](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) out — OGHaza, Nov 25 '13 at 21:03

M.Ali · Answer 1 · 2013-11-25T21:13:14.140

0

SELECT DTH.ActivityDate, STUFF(List, 1 ,2, '')
 FROM DailyTaskHours DTH
                CROSS APPLY ( SELECT ', ' + CAST(DTH.ActivityDate AS NVARCHAR) [text()]
                              FROM DailyTaskHours
                              WHERE DTH.ActivityDate = 1 
                              AND SomeRefrencingColum = DTH.SomeRefrencingColum
                              FOR XML PATH('')
                            ) Q(List)

edited Nov 25 '13 at 21:13

answered Nov 25 '13 at 21:05

M.Ali

67,945
13
101
127

This is still returning multiple rows. – David Tunnell Nov 25 '13 at 21:06

score 0 · Answer 2 · answered Nov 25 '13 at 21:12

You can append to a varchar with something similar to the following. Incidentally, the CASE statement in your code is unnecessary. The double booked flag will always be 1 because you are specifying that in your WHERE clause.

DECLARE @test varchar(max) 
SELECT @test = ''

SELECT @test = @test + '|' + CONVERT(varchar, DTH.ActivityDate, 101)  FROM DailyTaskHours DTH

SELECT @test

Deepika Janiyani · Answer 3 · 2013-11-26T09:49:00.863

You can use GROUP_CONCAT function to get the result in one field as follows,

SELECT GROUP_CONCAT(
        (CASE WHEN DTH.DoubleBookedFlag=1 THEN DTH.ActivityDate END)
     SEPARATOR ' | ') 
as DailyTaskHours
FROM 
  DailyTaskHours as DTH
WHERE 
   DTH.DoubleBookedFlag = 1

Moreover the below query will also give the same result, CASE is not needed to get the output as we are specifying the same condition in Where clause

SELECT GROUP_CONCAT(DTH.ActivityDate SEPARATOR ' | ') as DailyTaskHours
FROM 
DailyTaskHours as DTH
WHERE 
DTH.DoubleBookedFlag = 1

demo of both queries at http://sqlfiddle.com/#!2/19651/23

Concatenating multiple dates into a single varchar cell

3 Answers3