I want to make a query in sql-server which can make the following output as like column _B from column_A.Columns are varchar type.
Column_A column_B
karim,karim,rahim,masud,raju,raju karim,rahim,masud,raju
jon,man,jon,kamal,kamal jon,man,kamal
c,abc,abc,pot c,abc,pot
First of all: You were told in comments alread, that this is a very bad design (violating 1.NF)! If you have the slightest chance to change this, you really should... Never store more than one value within one cell!
If you have to stick with this (or in order to repair this mess), you can go like this:
This is the simplest approach I can think of: Transform the CSV to an XML and call XQuery-function distinct-values()
DECLARE @tbl TABLE(ColumnA VARCHAR(MAX));
INSERT INTO @tbl VALUES
('karim,karim,rahim,masud,raju,raju')
,('jon,man,jon,kamal,kamal')
,('c,abc,abc,pot');
WITH Splitted AS
(
SELECT ColumnA
,CAST('<x>' + REPLACE(ColumnA,',','</x><x>') + '</x>' AS XML) AS TheParts
FROM @tbl
)
SELECT ColumnA
,TheParts.query('distinct-values(/x/text())').value('.','varchar(250)') AS ColumnB
FROM Splitted;
The result
ColumnA ColumnB
karim,karim,rahim,masud,raju,raju karim rahim masud raju
jon,man,jon,kamal,kamal jon man kamal
c,abc,abc,pot c abc pot
WITH Splitted AS
(
SELECT ColumnA
,CAST('<x>' + REPLACE(ColumnA,',','</x><x>') + '</x>' AS XML) AS TheParts
FROM @tbl
)
SELECT ColumnA
,STUFF(
(TheParts.query
('
for $x in distinct-values(/x/text())
return <x>{concat(",", $x)}</x>
').value('.','varchar(250)')),1,1,'') AS ColumnB
FROM Splitted;
The result
ColumnB
karim,rahim,masud,raju
jon,man,kamal
c,abc,pot
XQuery functions (same with data()) But this is always a blank by default...Pseudocode: Make a postgresql function that receives as input the comma delimited string, and creates another array in memory. Split the string on comma, trim whitespace and enumerate each item, if the item doesn't appear in the new list, then add it. Finally flatten the new array to string and return.
drop function if exists remove_duplicates_from_comma_separated_string(text);
CREATE or replace FUNCTION remove_duplicates_from_comma_separated_string(arg1 text)
RETURNS text language plpgsql AS $$ declare
item text;
split_items text[];
ret_items text[];
ret_val text;
BEGIN
--split your string on commas and trim whitespace
split_items := string_to_array(ltrim(arg1), ',');
--enumerate each item, if it doesn't exist in the new array then add it.
FOREACH item IN ARRAY split_items LOOP
if ( item::text = ANY(ret_items)) then
else
--append this unique item into ret_items
select array_append(ret_items, ltrim(item)) into ret_items;
end if;
END LOOP;
--flatten the final array to a text with comma delimiter
SELECT array_to_string(ret_items, ',', '*') into ret_val;
return ret_val;
END; $$;
So now we can invoke the function on a table thustly:
drop table if exists foo_table;
create table foo_table(name text);
insert into foo_table values('karim,karim,rahim,masud,raju,raju');
insert into foo_table values('jon,man,jon,kamal,kamal');
insert into foo_table values('jon,man,kamal');
insert into foo_table values('c,abc,poty');
insert into foo_table values('c,abc,abc,kotb');
select remove_duplicates_from_comma_separated_string(name) from foo_table;
Which prints:
┌───────────────────────────────────────────────┐
│ remove_duplicates_from_comma_separated_string │
├───────────────────────────────────────────────┤
│ karim,rahim,masud,raju │
│ jon,man,kamal │
│ jon,man,kamal │
│ c,abc,poty │
│ c,abc,kotb │
└───────────────────────────────────────────────┘
Code smell haaax factor: 9.5 of 10. Construction crew watches the novice programmer bang in a nail with the $90 sql brand pipe wrench, everyone rolls their eyes.
