How to delete duplicates after STRING_AGG?

Question

First of all i've to tell you that i'm a beginner in SQL.

I want to select all the informations related to my PLAN thanks to it's id to put them in a DataGridView in my C# program.

We can affect multiples voltage levels, catenary types etc... to this plan and it would be stored in a table called plans_into_voltage and plans_into_type there.

So when I try to selecet all the informations related to a plan, if it has multiple voltage and/or many catenary types, it would duplicate rows like this :

results example

Then I tried to STRING_AGG but now I have duplicates into the row and I don't know how to avoid that/remove them..

This is my query :

SELECT TOP 20 pinfos.id
     , STRING_AGG(pinfos.plan_name, '|') 
     , STRING_AGG (voltage.name, '|') AS vname 
     , STRING_AGG(catType.name, '|') AS catname
FROM [allocation_schematic.information].primary_infos pinfos
     LEFT JOIN [allocation_schematic.types].plans_into_type pit 
            ON pinfos.id = pit.id_plan
     LEFT JOIN [allocation_schematic.types].catenary_type catType 
            ON pit.id_type = catType.id
     LEFT JOIN [allocation_schematic.types].plans_into_voltage pot 
            ON pinfos.id = pot.id_plan
     LEFT JOIN [allocation_schematic.types].voltage voltage 
            ON pot.id_voltage = voltage.id
GROUP BY pinfos.id

Results

But i want it like:

this

I've already check this first solution and this second one but I don't understand how to implement it with my query.

Could someone help me with this please ? I've been on this trouble since 2 days..

Thanks !

score 4 · Accepted Answer · edited Jun 20 '20 at 09:12

4

How about try this query:

SELECT TOP 20 pinfos.id
    , STRING_AGG(pinfos.plan_name, '|') 
     , STRING_AGG (voltage.name, '|') AS vname 
     , STRING_AGG(catType.name, '|') AS catname
FROM (
    SELECT  DISTINCT pinfos.id 
                    pinfos.plan_name,
                    voltage.name,
                    catType.name
    FROM [allocation_schematic.information].primary_infos pinfos
     LEFT JOIN [allocation_schematic.types].plans_into_type pit 
            ON pinfos.id = pit.id_plan
     LEFT JOIN [allocation_schematic.types].catenary_type catType 
            ON pit.id_type = catType.id
     LEFT JOIN [allocation_schematic.types].plans_into_voltage pot 
            ON pinfos.id = pot.id_plan
     LEFT JOIN [allocation_schematic.types].voltage voltage 
            ON pot.id_voltage = voltage.id
)
GROUP BY pinfos.id

This query is modified accordding this statement:

SELECT STRING_AGG(data)
FROM (
   SELECT DISTINCT FROM ...
)

I tested with my table and it works.

All data in my table employee:

Run my demo query:

SELECT emp.deptno, STRING_AGG(emp.ename,',') AS ename from (
    SELECT DISTINCT ename, deptno  FROM employee) as emp
GROUP BY emp.deptno

Update:

This query does't work as expected.

But Congratulations that you have solved it:

Used multiple queries and joined them thanks to a Dictionary in my c# Program and it works fine now

Hope this helps.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jul 15 '19 at 08:40

Leon Yue

15,693
1
11
23

Thanks for your help, I think we're on the right way but I'm still having errors. 1) I had to add `AS ptest` between ')' and 'GROUP BY pinfos.id' because it was saying the syntax was incorrect. 2) Now with the "correct" syntax i'm having the SQL error 4104 : The multi-part identifier "pinfos.id" could not be bound. I tried some things with no success, maybe you'll see where is the problem. – tsoich Jul 15 '19 at 12:49
I solved all the errors, the query looks like this now : https://pastebin.com/sDRCgQfh Unfortunately, it didn't work, i have the same results as before – tsoich Jul 15 '19 at 13:43
Did you run the `SELECT DISTINCT pinfos.id AS planid, pinfos.plan_name AS planname...` statement to test if the duplicate values have been excluded? – Leon Yue Jul 16 '19 at 00:53
Not really, i get [this](https://ibb.co/18KnLHb) when running the SELECT DISTINCT query only. – tsoich Jul 16 '19 at 07:39
I guess this is caused by the more table left join. Maybe `SELECT DISTINCT` only work for one table, or try to change LEFT JOIN, test again. – Leon Yue Jul 16 '19 at 08:32
Tried every kind of JOIN with no success. I thought to make an algorithm/function in SQL to delete between the delimiters what is exceeding. Do you think it's a good idea ? – tsoich Jul 16 '19 at 13:50
All the examples we found is worked for one table with duplicate value in row. Since `SELECT STRING_AGG() FROM ( SELECT DISTINCT...)` doesn't work for your 4 table, just try your new idea now. If the algorithm/function works, that will be very useful for us. – Leon Yue Jul 17 '19 at 01:05
Sorry for replying late, I've tested some things (that didn't work) but I didn't have enough time to think about this function. So i just used multiple queries and joined them thanks to a Dictionary in my c# Program and it works fine now – tsoich Jul 22 '19 at 07:33
@tsoich It doesn't matter. Congratulations that you have solved it ! I will update and add this to my answer, then others can know your problem solved know. Can you think about mark it as the answer?. This can be beneficial to other community members. Thank you. – Leon Yue Jul 23 '19 at 00:55
Done, thank you too. Maybe a user will find an SQL solution for this – tsoich Jul 23 '19 at 07:03

How to delete duplicates after STRING_AGG?

1 Answers1