Distributing expression between commas in multiple rows

Question

I have a SQL table in a Postgres server with several names per id and I need to distribute them by rows. The file is like that:

   | Id       | Names                       |
   | AA00001X | "Agent_1, Agent_2, Agent_3" |
   | BA00002X | "Agent_1, Agent_6"          |
   | CA00002X | "Agent_4, Agent_2"          |
   | DA00001Y | "Agent_2"                   |

What I need is a table like this:

| Id        | Name      |
| AA00001X  | "Agent_1" |
| AA00001X  | "Agent_2" |
| AA00001X  | "Agent_3" |
| BA00002X  | "Agent_1" |
| BA00002X  | "Agent_6" |
| CA00002X  | "Agent_4" |
| CA00002X  | "Agent_2" |
| DA00001Y  | "Agent_2" |

I tried to use a R interface and to build a code that would be able to do that. Nevertheless, the initial file is huge (3.1 Go) and R is not able to deal with it due to my configuration. I think it would be easier to do it directly on SQL but I am not expert in SQL code.

score 3 · Accepted Answer · 2019-10-07T09:25:06.583

3

You can convert the list to an array and then unnest the array:

select t.id, trim(x.name) as name
from the_table t
  cross join lateral unnest(string_to_array(trim(both '"' from t.names), ',')) as x(name)
order by t.id;

trim(both '"' from t.names) removes the double quotes from the comma separated string before converting it into an array.

Online example: https://rextester.com/KGD2704

edited Oct 07 '19 at 09:25

answered Oct 07 '19 at 08:22

Thanks for the help - I am currently trying the query. Just for my knowledge, what the trim(x.name) stand for? Why is it not trim (t.name)? – Plantekös Oct 07 '19 at 09:22
@Plantekös: `x.name` refers to the columns from the `unnest()` and that might contain spaces (because of the spaces around the `,` in your input data). There is no column `t.name` because in the base table it's called `names` – Oct 07 '19 at 09:24

score -1 · Answer 2 · answered Oct 07 '19 at 08:21

-1

Try this:

SELECT DISTINCT T2.id, 
    SUBSTRING(
        (
            SELECT ','+T1.Name  AS [text()]
            FROM dbo.mytable T1
            WHERE T1.id = T2.id
            ORDER BY T1.id
            FOR XML PATH ('')
        ), 2, 1000) [Names]
FROM dbo.mytable T2

You have to use your tablename instead of "mytable"

You can also use one of the answers in this post: How to concatenate strings of a string field in a PostgreSQL 'group by' query?

answered Oct 07 '19 at 08:21

Sebastian Siemens

2,302
1
17
24

That code is invalid for Postgres (and invalid standard SQL) and the goal is to split the comma separated string into multiple rows, not the other way round – Oct 07 '19 at 08:28

Distributing expression between commas in multiple rows

2 Answers2