This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a script to perform concatenation of strings by applying GROUP BY in SQL Server.
Yesterday, I was preparing one of the reports for the Production server and required the merging of strings basis on GROUP BY columns.
I used only FOR XML PATH to get this solution.
First, create the sample table and data:
|
1 2 3 4 5 6 7 |
CREATE TABLE tbl_GroupStringTable ([EmpID] INT, [EmpName] VARCHAR(250), [DeptName] VARCHAR(250)) INSERT INTO tbl_GroupStringTable ([EmpID],[EmpName],[DeptName]) VALUES (1,'Anvesh','Sales') INSERT INTO tbl_GroupStringTable ([EmpID],[EmpName],[DeptName]) VALUES (1,'Neevan','Production') INSERT INTO tbl_GroupStringTable ([EmpID],[EmpName],[DeptName]) VALUES (2,'Akash','Order') INSERT INTO tbl_GroupStringTable ([EmpID],[EmpName],[DeptName]) VALUES (3,'Roy','Sales') INSERT INTO tbl_GroupStringTable ([EmpID],[EmpName],[DeptName]) VALUES (3,'Priyaj','Order') |
Execute this script:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT [EmpID], STUFF(( SELECT ', ' + [EmpName] + ':' + CAST([DeptName] AS VARCHAR(MAX)) FROM tbl_GroupStringTable WHERE (EmpID = Results.EmpID) FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)') ,1,2,'') AS NameValues FROM tbl_GroupStringTable Results GROUP BY EmpID |
The Result:
