Problem in Concat String in multiple rows

Question

Dear All,

I encounter a concat string problem in T-SQL as following:

DB: SQL Server 2000 / 05 / 08
Table : t_person
Person & Date is composed primary key

Person    Date               Remark
ABC       2009-09-01      A
ABC       2009-09-03      apple.
ABC       2009-09-02      for 
ABC       2009-10-03      boy.
ABC       2009-10-02      for
ABC       2009-10-01      B
DEF        2009-09-01      123
DEF        2009-09-02      456

Expected Result:
Person   Period             Summary
ABC       2009-09          A for apple.
ABC       2009-10          B for boy.
DEF        2009-09          123456

Can i get the result with SQL statement only, but not use Stored Procedure to solve?

Many thanks!!

Multiplug

Answer 1

refer to http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/18a4aa86-cfe2-4e5d-8d37-b6e616af3bbd

---

KH Tan

Answer 2

Try this

SELECT T1.person,CONVERT(VARCHAR(7),DATE,120) AS Period  
           ,summary = SubString (( SELECT ' ' + T2.remark 
FROM T as T2 
WHERE T1.person = T2.person
AND CONVERT(VARCHAR(7),t1.DATE,120) = CONVERT(VARCHAR(7),t2.DATE,120)
FOR XML PATH ( '' ) ), 3, 1000) 
FROM T as T1 
GROUP BY t1.person, CONVERT(VARCHAR(7),t1.DATE,120)

Comma Delimited List

---

Mangal Pardeshi BI
SQL With Mangal

Answer 3

oh ok. Need to group by Person, dateadd(month, datediff(month, 0, Date), 0)

---

KH Tan

Answer 4

Thanks everybody