Get table column value as comma separated value (CSV) in SQL

The most common problem to get the table column value as CSV(Comma separated value) in SQL and it gets more complex when we need to get it while getting information from multiple tables. So today i am going to elaborate how we can get the CSV value with other information. So for example we have two tables as shown below :

Table A                       Table B
IDA         NameA               IDB   NameB  IDA

1             Name1                    1    NameB1    3
2             Name2                    2    NameB2    3
3             Name3                    3    NameB3    4
4             Name4                    4    NameB4    4
5             Name5                    5    NameB5    4

There are two ways to get the value as comma separated (create same table on your test database and execute the following script as it is :

DECLARE @Names VARCHAR(8000)
select @Names= COALESCE(@Names + ‘, ‘, ”) + a.NameA
from tableA a join tableB b on a.IDA = b.IDA
print @Names
Output :
csv2

 

WITH CTE_CSV (NameA)
AS
(
SELECT SUBSTRING((SELECT ‘,’ + NameA FROM TableA
FOR XML PATH(”)),2,10000) AS CSV
)
select NameA from CTE_CSV
Output :
csv

My Expected output is:
NameA      NameB
Name3      NameB1, NameB2
Name4      NameB3, NameB4, NameB5

more code will be added soon…….

Get table column value as comma separated value (CSV) in SQL

Delete Duplicate Records from Table in SQL

One day i got the scripts from DBA but at last i found that scripts added duplicate rows in the table and i got stuck to remove those duplicate rows from that table and finally i found a proper solution from the following link it helps and solved my problem. i am sharing this to every body it may help others as well.

found a solution from the following link :

http://stackoverflow.com/questions/18256201/sql-server-2008-delete-duplicate-rows

if you’ve any question regarding the following code i will try to answer every question

You can DELETE from a cte:

WITH cte AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY uniqueid ORDER BY col2)'RowRank'
             FROM Table)
DELETE FROM cte 
WHERE RowRank > 1

The ROW_NUMBER() function assigns a number to each row. PARTITION BY is used to start the numbering over for each item in that group, in this case each value of uniqueid will start numbering at 1 and go up from there. ORDER BY determines which order the numbers go in. Since each uniqueid gets numbered starting at 1, any record with a ROW_NUMBER() greater than 1 has a duplicate uniqueid

To get an understanding of how the ROW_NUMBER() function works, just try it out:

SELECT *,ROW_NUMBER() OVER(PARTITION BY uniqueid ORDER BY col2)'RowRank'
FROM Table
ORDER BY uniqueid

You can adjust the logic of the ROW_NUMBER() function to adjust which record you’ll keep or remove.

For instance, perhaps you’d like to do this in multiple steps, first deleting records with the same last name but different first names, you could add last name to the PARTITION BY:

WITH cte AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY uniqueid, col3 ORDER BY col2)'RowRank'
             FROM Table)
DELETE FROM cte 
WHERE RowRank > 1
Delete Duplicate Records from Table in SQL