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
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


Comments

  1. 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..

    ReplyDelete
  2. yes 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.

    here 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

    ReplyDelete

Post a Comment

Popular posts from this blog

Hadoop - Hive - Load data from csv/xls files

Microsoft BI Implementation - Cube back up and restore using XMLA command