1

Have a comma separated string in my table field (converted to a comma separated string using STRING_AGG with the user_id). Wish to separate the string into separate columns in the output. Is there some way to do this using PostgreSQL?

I am unable to use CROSSTAB() on this, as I am running the query in Amazon QuickSight, which does not allow the function: PostgreSQL Crosstab Query

Have tried using the SPLIT_PART(), but as I am not sure how many values there will be in each row, and also want that each column should have the same word in it, or be blank, if that word is not there in the row. The original SQL to convert the individual rows to a comma separated value is:

    SELECT 
        ss.ss_uuid,
        STRING_AGG(mn.name, ', ') AS markers

    FROM serial_state AS ss
    JOIN marker_names AS mn
    ON mn.s_uuid = ss.mn_s_uuid
    GROUP BY ss.ss_uuid

Output of Query 1:

| ss_uuid | markers       |
| abcdefg | my,words,here |
| bcdefgh | words,my      |
| fghijkl | my,here       |
| opqrstu | here.my       |
| xyzabcd | here,my,words |
| pqrstuv | words         |
| restgte | my,you,here,i |

I have used the following to get the same into a column:

SELECT
    split_part(markers, ',', 1) AS "Marker Name1",
    split_part(markers, ',', 1) AS "Marker Name2",
    split_part(markers, ',', 1) AS "Marker Name3"
FROM ( 
    SELECT 
        ss.ss_uuid,
        STRING_AGG(mn.name, ', ' ORDER BY mn.name) AS markers

    FROM serial_state AS ss
    JOIN marker_names AS mn
    ON mn.s_uuid = ss.mn_s_uuid
    GROUP BY ss.ss_uuid
) t

Output for query 2:

| Marker Name 1 | Marker Name 2 | Marker Name3 |
| my            | words         | here         |
| words         | my            |              |
| my            | here          |              |
| here          | here          |              |
| here          | my            | words        |
| words         |               |              |
| my            | you           | here         |  -- i is missing from this row

But this query has the 2 issues as highlighted above:

  • Do not know how many words there are in the string, so if there are more than 3, then I miss out
  • As the initial 'comma separated' string will be not always be composed of the same words, the "Marker Name1" can contain names which are different.

Is there any way that I can resolve this, with a loop (IF/WHERE/WHILE clauses?) or something else?

Update 2:

Output I need:

| Marker Name 1 | Marker Name 2 | Marker Name3 | Marker Name4 | Marker Name5 |
| my            | words         | here         |
| my            | words         |              |
| my            |               | here         |
|               |               | here         |
|               | words         | here         |
|               | words         |              |
| my            |               | here         | you          | i            |

Update: Was thinking something along these lines:

lenOfString = STRING_TO_ARRAY(flags);
FOR i IN lenOfString LOOP
    SELECT
        split_part(markers, ',', 1) AS "Flag Name" 
    FROM ( 
        SELECT 
            ss.ss_uuid,
            STRING_AGG(mn.name, ', ') AS flags

        FROM serial_state AS ss
        JOIN marker_names AS mn
        ON mn.s_uuid = ss.mn_s_uuid
        GROUP BY ss.ss_uuid, mn.name
    ) t
END LOOP

But that gives an error for the lenOfString

Sources checked for answers on StackOverflow:

SQL Server 2014 : Convert two comma separated string into two columns

convert comma separated string to sql table in SP

Convert comma separated string to numeric columns

gagneet
  • 35,729
  • 29
  • 78
  • 113
  • 1
    `regexp_split_to_table`? – Laurenz Albe Jun 02 '21 at 05:53
  • I think similar to https://stackoverflow.com/questions/67742978/postgresql-join-flatten-result/67743851#67743851 – Akhilesh Mishra Jun 02 '21 at 06:28
  • @AkhileshMishra Nope that requires a stored proc to be created, and then used. Alos, it does not resolve my second query as yet :-( – gagneet Jun 02 '21 at 09:18
  • You are seeking a dynamic query which is not possible in straight SQL syntax. Please elaborate your second point (with example). – Akhilesh Mishra Jun 02 '21 at 09:46
  • @AkhileshMishra I am using this in AWS (QuickSight). So I am unable to use items like a stored procedure. I can create a dataset and then use that, but will the commands that are there work for PostgreSQL? The second issue is the name in that column being the same and not a different one as shown in the example above. – gagneet Jun 02 '21 at 10:32

0 Answers0