15,994,059 members
See more:
Hi all,

I have a table like

EmpCode Leave Remark
May001 CL Urgent Work
May001 EL Holiday
May001 EL Weekly Off
May001 ML Mariage
May001 PL Going to Home
May001 PL Not feeling well

I need the result like

EmpCode Leave Remark
May001 CL Urgent Work
May001 EL Holiday + Weekly Off
May001 ML Mariage
May001 PL Going to Home + Not feeling well

Suppose that i have a lot of employees in table and i need to concatenate the remark for empcode and leave wise i.e. a employee and a leave have only one row.
I do not want to use local variable wise loop and update.

Posted

## Solution 1

Try this
SQL
```Selcet Leave,

(
SELECT SUBSTRING(
(
SELECT '+' + T2.Remark
FROM EmpTable T2
where T2.Leave=T1.Leave
FOR XML PATH('')),2,200000) AS Remark
) as Remark

from EmpTable as T1
Group by Leave```

v4
rakesh@bbspl.com 1-Aug-12 4:17am
Thanks but i need the result like table2. I only have table1.
This will give ouput like second table.
Query is using one table. Look but I have used two alias T1 & T2 for one table. Please try it & give what output you getting.

## Solution 2

try this.

SQL
```Select distinct Empcode,Leave,
(
SELECT SUBSTRING(
(
SELECT '+' + T2.Remark
FROM EMptest T2
where T2.Leave=T1.leave and T2.Empcode=T1.EmpCode
FOR XML PATH('')),2,200000) AS Remark
) as Remark
from EMptest as T1```

v2
Martijn Kok 1-Aug-12 7:02am
I wouldn't have thought about the XML PATH to solve this problem. After seeing your solution I have been trying some.

If you replace T2.Leave=T1.Leave with T2.EmpCode=T1.Empcode, the query will work.
Santhosh Kumar Jayaraman 1-Aug-12 7:04am
No i dont think, we should replace.
i hope we have to use both
where T2.Leave=T1.leave and T2.Empcode=T1.EmpCod
Martijn Kok 1-Aug-12 7:14am
You are right. Thanks. The example data only contains Leaves on May001. Joining on Leave and EmpCode is more generic.

*Martijn goes back to the SQL 101 class* :)
Santhosh Kumar Jayaraman 1-Aug-12 7:16am
ha ha..:)