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:
Hi Imran Bhai,
Really it help me a lot ......
Thanks for writting such valuable
notes.keep it....
Thanks & Regards
Debi Tripathy
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
@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.
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?
thank you for your knowledge
Thanks!! for the artical
Post a Comment