concatenation using group by

Question

0.00/5 (No votes)

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.

Thanks in advance

Posted 31-Jul-12 21:35pm

rakesh@bbspl.com

Add a Solution

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

pradiprenushe · Answer 1 · 2012-07-31T21:52:00

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

Posted 31-Jul-12 21:52pm

pradiprenushe

Updated 31-Jul-12 23:53pm

v4

Comments

rakesh@bbspl.com 1-Aug-12 4:17am

Thanks but i need the result like table2. I only have table1.

pradiprenushe 1-Aug-12 4:25am

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.

Santhosh Kumar Jayaraman · Answer 2 · 2012-07-31T22:50:00

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

Posted 31-Jul-12 22:50pm

Santhosh Kumar Jayaraman

Updated 1-Aug-12 1:04am

v2

Comments

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..:)

concatenation using group by

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

concatenation using group by

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us