Unnest row in PostgreSQL and include count of unnesting occurrences-CodePudding

Following on from Matt's answer in: Split column into multiple rows in Postgres

I want to unnest a row into multiple rows based on a character in a cell, however I also want to include a count column containing the number of times a row was unnested.

I want this:

       name            |  id
----------------------- ------
 alpha, bravo, charlie |    1
 yankee, xray          |    2
 hotel                 |    3 
 indigo                |    4

to become this:

       name            |  id  | count
----------------------- ------ -------
 alpha                 |    1 |   3
 bravo                 |    1 |   3
 charlie               |    1 |   3
 yankee                |    2 |   2
 xray                  |    2 |   2
 hotel                 |    3 |   1
 indigo                |    4 |   1

The initial unnest query would be:

SELECT unnest(string_to_array(name, ', ')) as name, id
FROM table

CodePudding user response：

You can use a cross join with regex:

select k, t.id, cardinality(regexp_split_to_array(t.name, ', ')) 
from tbl t cross join regexp_split_to_table(t.name, ', ') k

See fiddle.