Thursday, August 19, 2010

Concatenating Row Values into one column in Oracle, SQL Server Database and LINQ

Concatenating Row Values into one column in Oracle, SQL Server Database and LINQ


Concatenating row values is very common requirements in different databases, to write this kind of query is very time consuming, I tried to combine Oracle,SQL Server database and LINQ simple solution into below example. Maybe this is not matched with your requirement but you will get an idea to proceed.


Requirement


Let say you have one Employee table having FK reference with department table like below


Employee table

EMP_ID

DEPT_ID

1

101

2

101

3

102

4

102

5

102

How to write a select query so that it returns two columns DEPT_ID and Comma Separated employees under that department. Example


DEPT_ID

EMPLOYEES

101

1,2

102

3,4,5


Solution (ORACLE DATABASE)

To achieve above requirement there are different solution available in oracle different versions

Oracle 10g: Use COLLECT function like

SELECT DEPT_ID,

CAST (COLLECT (TO_CHAR(EMP_ID)) AS SYS.ODCIVARCHAR2LIST) AS EMPLOYEES

FROM EMPLOYEE

GROUP BY DEPT_ID


Note: If SYS.ODCIVARCHAR2LIST does not work then you have to create one table object, see below example

CREATE OR REPLACE TYPE Collect_Type AS TABLE OF VARCHAR2 (4000);

SELECT DEPT_ID,

CAST (COLLECT (TO_CHAR(EMP_ID)) AS Collect_Type) AS EMPLOYEES

FROM EMPLOYEE

GROUP BY DEPT_ID


Oracle 11g: Use LISTAGG function like

SELECT DEPT_ID,

LISTAGG (TO_CHAR(EMP_ID), ', ') WITHIN GROUP (ORDER BY DEPT_ID)as "EMPLOYEES"

FROM employees;


Ref: http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions087.htm


Result:

DEPT_ID

EMPLOYEES

101

1,2

102

3,4,5


Solution (SQL SERVER DATABASE)

SQL Server: SQL server database also you can achieve same result in multiple ways, like XML PATH or Custom Function etc.

Using XML PATH

Select Main.Dept_ID,

Left(Main.Employee,Len(Main.Employee)-1) As "Employees"

From(Select distinct ST2.Dept_ID,

(Select cast(ST1.Emp_ID as varchar(50)) + ',' AS [text()]

From dbo.Employee ST1

Where ST1.Dept_ID= ST2.Dept_ID

ORDER BY ST1.Dept_ID

For XML PATH ('')) [Employee]

From dbo.Employee ST2) [Main]


Using Custom Function

CREATE FUNCTION ConcatEmp(@DeptId varchar(50))

RETURNS VARCHAR(1000) AS

BEGIN

DECLARE @temp VARCHAR(1000)

SELECT @temp = COALESCE(@temp + ', ', '') + cast(Emp_ID as varchar(50))

FROM [Employee]

WHERE Dept_ID = @DeptId

return @temp

END


After creating above function you can simply execute in select statement like below


SELECT Dept_ID

, dbo.ConcatEmp(Dept_ID) AS Employees

FROM Employee

GROUP BY Dept_ID


Result:

DEPT_ID

EMPLOYEES

101

1,2

102

3,4,5


Solution (LINQ QUERY)

LINQ is one of the .NET (Framework 3.0 and above) features to write SQL query in code, if you are trying to achieve same result using LINQ, you can get idea from following example


Here I have created one small console application and write following code to get same results as SQL Server and Oracle Database


using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;


namespace ConsoleApplication1

{

public class Test

{

static void Main()

{

var Employee = new[]

{

new { Dept="101", EmpId="1" },

new { Dept="101", EmpId="2" },

new { Dept="102", EmpId="3" },

new { Dept="102", EmpId="4" },

new { Dept="102", EmpId="5" },

};


var groupedUsers = Employee.GroupBy(Dept => Dept.Dept);


foreach (var group in groupedUsers)

{

Console.Write(" {0} :", group.Key);

foreach (var entry in group)

{

Console.Write(" {0}", entry.EmpId);

}

Console.WriteLine();

}

Console.Read();

}

}

}


Result:

----------

101: 1,2

102: 3,4,5

2 comments:

Nandkishor Wagh said...

attractive piece of information, I had come to know about your blog from my friend arjun, ahmedabad,i have read atleast eleven posts of yours by now, and let me tell you, your website gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a lot once again, Regards, Single Row Function in sql


Nandkishor Wagh said...

attractive piece of information, I had come to know about your blog from my friend arjun, ahmedabad,i have read atleast eleven posts of yours by now, and let me tell you, your website gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a lot once again, Regards, Single Row Function in sql