String concatenation using FOR XML PATH() and STUFF

In this article, we will be discussing how to convert row data to column data with ',' separation.

We can fulfill this requirement with the help of  STUFF , Substring and FOR XML PATH() very easily.



Let's create a table with sample data.


CREATE TABLE ForXmlExp (ID INT, NAME VARCHAR(250)) 


INSERT INTO ForXmlExp VALUES (1,'Ganesh') 

INSERT INTO ForXmlExp VALUES (2,'Mohan') 

INSERT INTO ForXmlExp VALUES (3,'Raju') 

INSERT INTO ForXmlExp VALUES (4,'Kiran') 

INSERT INTO ForXmlExp VALUES (5,'Charan') 

INSERT INTO ForXmlExp VALUES (1,'Rajesh') 

INSERT INTO ForXmlExp VALUES (2,'Venkat') 

select table:

Select * from  ForXmlExp 

FOR XML PATH( )  clause returns the SQL results as XML elements.
By giving empty (' ') string in PATH argument and concatenated  ','  with Name column. we will get below results.

Query:

SELECT ',' + NAME FROM   ForXmlExp FOR xml path('')

OutPut:

,Ganesh,Mohan,Raju,Kiran,Charan,Rajesh,Venkat


In the above output we can see Row data converted to the column data, but one additional ',' is there in the first position. That we can remove using STUFF , Substring functions.


Query to remove leading comma.


SELECT Stuff((SELECT ',' + NAME 
              FROM   ForXmlExp 
              FOR XML PATH('')),1,1,'') 

OR

we can write the same logic with Substring As well.


SELECT Substring((SELECT ',' + NAME 
                  FROM   ForXmlExp 
                  FOR XML PATH('')), 1, 8000) 



Output:

 Ganesh,Mohan,Raju,Kiran,Charan,Rajesh,Venkat


As of now, we worked on a particular column, Now let's work on the table and get the result based on the ID column and applying  Group By on ID Column.


Query:

SELECT ID, 
       Stuff((SELECT ',' + NAME 
              FROM   ForXmlExp T1 
              WHERE  T1.ID = T2.ID 
              FOR XML PATH ('')), 1, 1, '') AS Result 
FROM   ForXmlExp T2 
GROUP  BY ID 


Output:

Thank you for visiting the blog, hope you got the required information.

How to identify the duplicate records and delete those records from the table.

In this article, we will be discussing how to delete duplicate records from the table. Here we are using
ROW_NUMBER()  function.

ROW_NUMBER() OVER(PARTITION BY [] ORDER BY [] )

Creating Table :

CREATE TABLE DELETEDUPLICATERECORD  (ID INT,NAME VARCHAR(250),MAIL VARCHAR(250), 
     ADDRESS VARCHAR(250),PINDODE INT ) 


Inserting Sample Data:

INSERT INTO DELETEDUPLICATERECORD 
VALUES (1,'Ganesh','Ganesh@gmail.com', 'Hyd','500000') 

INSERT INTO DELETEDUPLICATERECORD 
VALUES (2,'Mohan','Mohan@gmail.com','Hyd','500000') 

INSERT INTO DELETEDUPLICATERECORD 
VALUES  (3, 'Raju','Raju@gmail.com','Hyd', '500000') 

INSERT INTO DELETEDUPLICATERECORD 
VALUES  (4,'Kiran','Kiran@gmail.com','Hyd','500000') 

INSERT INTO DELETEDUPLICATERECORD 
VALUES  (5,'Charan','Charan@gmail.com','Hyd','500000') 

INSERT INTO DELETEDUPLICATERECORD 
VALUES  (6,'Ganesh','Ganesh@gmail.com','Hyd','500000') 

INSERT INTO DELETEDUPLICATERECORD 
VALUES  (7,'Mohan','Mohan@gmail.com','Hyd','500000') 

Select table:

SELECT * FROM   DELETEDUPLICATERECORD 










in the above image, we can see the table 'DELETEDUPLICATERECORD' having two duplicate records.

Now, let us identify those two records and delete them.

Query to identify duplicate  records:

SELECT *, 
       ROW_NUMBER() 
         OVER ( 
           partition BY NAME, MAIL 
           ORDER BY ID ) DuplicateCount 
FROM   DELETEDUPLICATERECORD 

OutPut:

In the above image, DuplicateCount column tells us how many duplicate records are there


Now filtering only duplicate records.

SELECT * 
FROM   (SELECT *, 
               ROW_NUMBER() 
                 OVER ( 
                   partition BY NAME, MAIL 
                   ORDER BY ID ) DuplicateCount 
        FROM   DELETEDUPLICATERECORD) T 
WHERE  DuplicateCount >= 2 


OutPut:

Deleting the records.


DELETE T 
FROM   (SELECT *, 
               ROW_NUMBER() 
                 OVER ( 
                   partition BY NAME, MAIL 
                   ORDER BY ID ) DuplicateCount 
        FROM   DELETEDUPLICATERECORD) T 
WHERE  DuplicateCount >= 2 

Final Results after deleting duplicate records.


SELECT * FROM   DELETEDUPLICATERECORD 

Thank you for visiting the Blog, hope you got the required information.