CODEWITHSUNDEEP
sqlpostgresqlstring-aggregation
pean
pean

Reputation: 532

Sort conditional data in Postgres STRING_AGG

I've come up with a query to create concatenated string based on a conditional statement using STRING_AGG in Postgress. That works just fine but I want to sort the result as well without duplicating the CASE.

This is what I have now:

SELECT
STRING_AGG(distinct name, ', ' ORDER BY name),
STRING_AGG(
  DISTINCT
  CASE WHEN something = true THEN 'type1'
       WHEN something_else = true THEN 'type2'
       ELSE 'type3'
  END, ', '
ORDER BY
  CASE WHEN something = true THEN 'type1'
       WHEN something_else = true THEN 'type2'
       ELSE 'type3'
  END

)
from organisations

..but I want to do something like this to not have the duplicated code and remove rows and complexity from my query but I can't figure out how to make it work, this is fake code that doesn't work obviously, but you get the idea:

SELECT
STRING_AGG(distinct name, ', ' ORDER BY name) names,
STRING_AGG(
  DISTINCT (
    CASE WHEN something = true THEN 'type1'
         WHEN something_else = true THEN 'type2'
         ELSE 'type3'
    END
  ) as types, ', ' ORDER BY types) types
from organisations

Upvotes: 1

Views: 2813

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

You don't require string_agg(). You could instead do:

SELECT STRING_AGG(distinct name, ', ' ORDER BY name) names,
       CONCAT_WS(',',
                 (CASE WHEN SUM( (something = true)::int ) > 0 THEN 'type1'),
                 (CASE WHEN SUM( (not (something = true) )::int ) > 0 THEN 'type2')
                ) as types
FROM organisations o;

You may have oversimplified the query, but for what you provided, you don't need string_agg(distinct) for the second portion.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520948

One option might be to compute the CASE expression first in a separate CTE, and then query that CTE to apply STRING_AGG:

WITH cte AS (
    SELECT
        name,
        CASE WHEN something = true THEN 'type1'
             WHEN something_else = true THEN 'type2'
             ELSE 'type2'
        END AS expr
    FROM organisations
)

SELECT
    STRING_AGG(distinct name, ', ' ORDER BY name),
    STRING_AGG(DISTINCT expr, ', ' ORDER BY expr)
FROM cte;

As a minor side note, since you have type2 as both the second and ELSE condition in the CASE expression, you might as well just use this:

CASE WHEN something = true THEN 'type1'
     ELSE 'type2'
END

Upvotes: 1

Related Questions