SQL - Concatenate multiple column values
Yesterday I got one requirement to concatenate multiple values of same column into one row based on the specified group. Initially I tried it with MS-Excel but it failed suddenly I switched my method to SQL server and finished the job in no time. I thought it will be better to share the code for future use.
Requirement
Due to restrictions in giving the actual requirement, masked the details and also reduced the total number of columns. In actual requirement there are millions of records are there and here I reduced the records to 6 for easy understanding. Look at the figure F 0.1
for the same customer and city there are multiple product serials and the output result should be like Fig 0.2
Solution
Create table with identity column in SQL server and insert the raw data into the table. Identity column helps us to go through the each rows in the table.
CREATE TABLE RequirementData
(
ctr INT IDENTITY(1,1),
[Customer] [NVARCHAR](255) NULL,
[city] [NVARCHAR](255) NULL,
[ProductSerial] [NVARCHAR](255) NULL,
) ON [PRIMARY]
Now execute the below code to get the desired result
CREATE TABLE #Result
(
ctr INT IDENTITY(1,1),
[Customer] [NVARCHAR](255) NULL,
city NVARCHAR(255),
ProductSerial NVARCHAR(MAX)
) ON [PRIMARY]
INSERT INTO #Result ([Customer],city)
SELECT DISTINCT [Customer],city FROM RequirementData -- only distinct records
---====================================================================================
DECLARE @ctr INT =0
DECLARE @custName VARCHAR(255),
@serialnum VARCHAR(MAX),
@city VARCHAR(255)
SELECT @ctr = COUNT(*) FROM #Result
WHILE @ctr >0
BEGIN
--select the customer and the city in each row. these are the grouping columns for product serials
SELECT @custName = [Customer],
@city = city
FROM #Result
WHERE ctr = @ctr
SELECT @serialnum = COALESCE(@serialnum + ', ', '') + [ProductSerial]
--coalesce concatenate the column into one row
FROM RequirementData
WHERE ISNULL([Customer],'') = ISNULL(@custName,'')
AND ISNULL([city],'') = ISNULL(@city,'')
UPDATE #Result
SET ProductSerial = @serialnum
WHERE ISNULL([Customer],'') = ISNULL(@custName,'')
AND ISNULL([city],'') = ISNULL(@city,'')
SET @serialnum = ''
SET @ctr = @ctr -1
END
SELECT
[productserial],
[Customer],
[city]
FROM #Result
Requirement
Due to restrictions in giving the actual requirement, masked the details and also reduced the total number of columns. In actual requirement there are millions of records are there and here I reduced the records to 6 for easy understanding. Look at the figure F 0.1
 |
| Fig 0.1 |
for the same customer and city there are multiple product serials and the output result should be like Fig 0.2
 | |
| Fig 0.2 |
Solution
Create table with identity column in SQL server and insert the raw data into the table. Identity column helps us to go through the each rows in the table.
CREATE TABLE RequirementData
(
ctr INT IDENTITY(1,1),
[Customer] [NVARCHAR](255) NULL,
[city] [NVARCHAR](255) NULL,
[ProductSerial] [NVARCHAR](255) NULL,
) ON [PRIMARY]
Now execute the below code to get the desired result
CREATE TABLE #Result
(
ctr INT IDENTITY(1,1),
[Customer] [NVARCHAR](255) NULL,
city NVARCHAR(255),
ProductSerial NVARCHAR(MAX)
) ON [PRIMARY]
INSERT INTO #Result ([Customer],city)
SELECT DISTINCT [Customer],city FROM RequirementData -- only distinct records
---====================================================================================
DECLARE @ctr INT =0
DECLARE @custName VARCHAR(255),
@serialnum VARCHAR(MAX),
@city VARCHAR(255)
SELECT @ctr = COUNT(*) FROM #Result
WHILE @ctr >0
BEGIN
--select the customer and the city in each row. these are the grouping columns for product serials
SELECT @custName = [Customer],
@city = city
FROM #Result
WHERE ctr = @ctr
SELECT @serialnum = COALESCE(@serialnum + ', ', '') + [ProductSerial]
--coalesce concatenate the column into one row
FROM RequirementData
WHERE ISNULL([Customer],'') = ISNULL(@custName,'')
AND ISNULL([city],'') = ISNULL(@city,'')
UPDATE #Result
SET ProductSerial = @serialnum
WHERE ISNULL([Customer],'') = ISNULL(@custName,'')
AND ISNULL([city],'') = ISNULL(@city,'')
SET @serialnum = ''
SET @ctr = @ctr -1
END
SELECT
[productserial],
[Customer],
[city]
FROM #Result
Interesting!...I would like to know how much time it took when you applied this logic in real scenario? We can use 'FOR XML' for the same purpose.. No need of while loop and all.. it will work in the select itself..
ReplyDeleteyes we can do it using FOR XML but it took more time than the while loop. I tried the performance with the sample data before using it in actual report. for 200 records while loop took .001 seconds whereas For XML took .01 seconds.
ReplyDeletehere is the code for same tables for testing.
SELECT
DISTINCT RD2.[Customer],[City],
SUBSTRING((SELECT ','+RDraw.[ProductSerial] AS [text()]
FROM dbo.RequirementData RDraw
WHERE RDraw.[Customer] = RD2.[Customer]
AND RDraw.[city] = RD2.[city]
ORDER BY RDraw.[Customer],RDraw.city
FOR XML PATH ('')),2, 8000) Requirement
FROM dbo.RequirementData RD2
Cool...
Delete