Overblog Follow this blog
Edit post Administration Create my blog
Balavardhan Reddy Narani

Concatenating Row Values in Transact-SQL

April 7 2009 , Written by Balavardhan Reddy Published on #SQL Server2005

Concatenating Row Values in Transact-SQL

There are a number of solutions and considerable debate. How do you go about Concatenating Row values.

By 'Concatenating row values' we mean this: You have a table, view or result that looks like below......and you wish to have a resultset like the one below:

Ex:  

Table Structure :

Jobs : Job_ID, Job_Title
Employee :Emp_ID, Emp_name, Job_ID  
-- Job ID is Foreign Key 

Tables Data looks like :

Jobs

Job_ID            Job_Title
   1              Grade - A
   2              Grade - B
   3              Grade - C

Employee

Emp_ID                           Emp_name                  Job_ID
1                                 Emp1                        1 
2                                 EMP2                        1
3                                 EMP3                        1  
4                                 EMP7                        2
5                                 EMP4                        2  
6                                 EMP8                        3
7                                 EMP9                        3  
8                                 EMP5                        1


Required Result Set :

Job_ID                        Emp_Names
1                            EMP1,EMP2,EMP3,Emp5
2                            EMP7,EMP4
3                            EMP8,EMP9




The XML method

Here is a technique for string concatenation that uses the FOR XML clause with PATH mode.

SELECT  E1.Job_ID,
       ( SELECT E2.Emp_name + ','
          FROM  Employee E2
          WHERE E2.Job_ID= E1.Job_ID
          ORDER BY E2.Emp_Name
          FOR XML PATH('') ) AS EmpNames
FROM Employee E1
GROUP BY Job_ID


 

Scalar UDF with variable concatenation in SELECT

CREATE  FUNCTION udf_update_concat (@jobid INT) RETURNS VARCHAR(MAX) AS

BEGIN

DECLARE  @t TABLE(p VARCHAR(40))
DECLARE  @r VARCHAR(MAX)
SET @r = SPACE(0)

INSERT  @t ( p )
SELECT Emp_name FROM Employee WHERE Job_ID = @jobid

IF @@ROWCOUNT > 0
    UPDATE  @t

SET  @r = @r + p + ','
RETURN(@r)
END


--Here is how to use this function:

SELECT  Job_ID, dbo.udf_update_concat(Job_ID)
 FROM Employee
 GROUP BY  Job_ID


 

Share this post

Repost 0

Comment on this post

Muay Thai 01/14/2012 18:43


Good content, thanks for the code.