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 :

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

But i want it like:

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 !

Answer 1

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.