Search code examples
sqlpostgresqlcsvcountlateral-join

sql how to convert multi select field to rows with totals

I have a table that has a field where the contents are a concatenated list of selections from a multi-select form. I would like to convert the data in this field into in another table where each row has the text of the selection and a count the number of times this selection was made. eg.

Original table:

id    selections
1     A;B
2     B;D
3     A;B;D
4     C

I would like to get the following out:

selection   count
A           2
B           3
C           1
D           2

I could easily do this with split and maps in javascript etc, but not sure how to approach it in SQL. (I use Postgresql) The goal is to use the second table to plot a graph in Google Data Studio.

Solution

  • A much simpler solution:

    select regexp_split_to_table(selections, ';'), count(*)
from test_table
group by 1
order by 1;