Selecting/updating single field from duplicates

Question

This is my Sql:

SELECT * FROM parameter1
WHERE parameter1_ID IN (34,11)

this results in over 400 results (which is normal), most results have multiple values in VALUE column and some of the results have duplicate and multiple values in the VALUE column. I want to get rid of the duplicate VALUEs within their respective results, what can I add to this query to do so? Using MS SQL studio

example result from query:

ID   VALUE
1    100,200
2    100,100,200
3    200,200,300
4    200,200,300

result I want

ID   VALUE
1    100,200
2    100,200
3    200,300
4    200,300

Does the `VALUE` column contain comma-separated list of values as a string (e.g. `varchar`)? Is it na array (e.g. in Postgres)? Is it a result of join with another table? — Archie, Aug 04 '20 at 08:16
yes, it contains comma seperated list of values. and yes it is varchar. — Nugget, Aug 04 '20 at 08:18
You should [edit] your question and add the tag for which DBMS you are using. Are you using Oracle? Are you using SQL Server? Are you using PostgreSQL? The solution may require a built-in database function and these are not standard across different DBMS's — Abra, Aug 04 '20 at 08:20
Im not sure but it should be an sql server. Im new to this, how can I be sure — Nugget, Aug 04 '20 at 08:25
Im working with a premade database, I don't really have a choice on this. — Nugget, Aug 04 '20 at 08:30
Does this answer your question? [How to delete duplicate rows in SQL Server?](https://stackoverflow.com/questions/18390574/how-to-delete-duplicate-rows-in-sql-server) — JeffUK, Aug 04 '20 at 08:58
No, I found a lot of similar ones to that and I don't want to remove rows — Nugget, Aug 04 '20 at 09:04
Please read [this](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. — HABO, Aug 04 '20 at 14:37

score 0 · Accepted Answer · answered Aug 04 '20 at 09:18

As many others have said, the way you are storing data is very bad practice and will almost certainly cause many more headaches in the future.

That said, if you are actually unable to change this there are still options for you. In SQL Server 2017 you have the benefit of string_split and string_agg:

declare @t table(ID int, val varchar(40))
insert into @t values
 (1,'100,200')
,(2,'100,100,200')
,(3,'200,200,300')
,(4,'200,200,300');

with s as
(
    select distinct t.ID
                   ,s.[value] as val
    from @t as t
        cross apply string_split(t.val,',') as s
)
select s.ID
      ,string_agg(s.val,',') as val
from s
group by s.ID;

Output

+----+---------+
| ID |   val   |
+----+---------+
|  1 | 100,200 |
|  2 | 100,200 |
|  3 | 200,300 |
|  4 | 200,300 |
+----+---------+

When I try running the code you gave it gives me an error `Msg 208, Level 16, State 1, Line 8 Invalid object name 'string_split'.` how can I change it to fit my case? — Nugget, Aug 04 '20 at 09:48
@Nugget Is your databsae running at a lower compatibility level than 2017? If so, neither `string_split` or `string_agg` will work for you, but there are numerous resources online that will show you how to implement the same logic in more vanilla SQL. — iamdave, Aug 04 '20 at 10:02

Selecting/updating single field from duplicates

1 Answers1

Output