Search

Dec 16, 2008

String Concatenation in SQL Group By clause

I got one problem in Ahmedabad SQLServer UserGroup.

The problem was something like... there are grouped data and requirement was making sum of string collection, means concatenation of string which are in the same group. Here is the question along with solutionSource Table

Field1     Field2
---------- ----------
Group 1 Member 1
Group 1 Member 2
Group 1 Member 3
Group 2 Member 1
Group 2 Member 2
Group 3 Member 1
Group 3 Member 2


Need output as

Field1     Field2
---------- ----------------------------
Group 1 Member 1,Member 2,Member 3
Group 2 Member 1,Member 2
Group 3 Member 1,Member 2


Solution:

SET NOCOUNT ON

--Creating Tables
DECLARE @Fields AS TABLE
(
Field1 VARCHAR(10),
Field2 VARCHAR(10)
)

--Inserting some values
INSERT INTO @Fields VALUES ('Group 1','Member 1')
INSERT INTO @Fields VALUES ('Group 1','Member 2')
INSERT INTO @Fields VALUES ('Group 1','Member 3')
INSERT INTO @Fields VALUES ('Group 2','Member 1')
INSERT INTO @Fields VALUES ('Group 2','Member 2')
INSERT INTO @Fields VALUES ('Group 3','Member 1')
INSERT INTO @Fields VALUES ('Group 3','Member 2')

--T-Sql
SELECT Field1, Substring(Field2, 2, LEN(Field2)) AS Field2 FROM
(
SELECT
[InnerData].Field1,
(SELECT ',' + Field2 FROM @Fields WHERE Field1=[InnerData].Field1 FOR XML PATH('')) AS Field2
FROM
(
SELECT DISTINCT Field1 FROM @Fields
) AS [InnerData]
) AS OuterData

6 comments:

Debicharan Tripathy said...

Hi Imran Bhai,
Really it help me a lot ......
Thanks for writting such valuable
notes.keep it....
Thanks & Regards
Debi Tripathy

stephen said...

pls check this

declare @fullname as varchar(200)
select @fullname = COALESCE(@fullname + ',', '') + FullName from vwemployeemaster a where a.empid in (1,2,3)

select @fullname

Imran said...

@stephen:
Well you can do that, but in your example employee id is static, and if I want to make it generic I have to use one mroe select query in IN caluse. And using IN clause it hitting the performance.

Ryan said...

This appears to work quiete well, but it is a bit messy.
Oracle has the wm_concat string function. Is there anything similar to this function in SQL Server?

Rbus said...

thank you for your knowledge

Anonymous said...

Thanks!! for the artical