I have a database table:

| id | account | extra_account | state |special_value

I need to select extra_accounts, which are connected with a list of accounts.

SELECT * FROM table
WHERE table.account in (111, 222, 333) and table.state = 'WORKS';

|id  | account         | extra_account     | state         |special_value
—-------------------------------------------------------------------------------
|100 |111              |111-1              |WORKS          |1
|200 |111              |111-2              |WORKS          |1
|300 |222              |222-1              |WORKS          |2
|400 |333              |333-1              |WORKS          |3
|500 |333              |333-2              |WORKS          |4

I have to concatenate extra_accounts in one String separated by a comma.

If an account has two or more extra_accounts and their special_value are the same I have to take one extra_accounts, no matter which. So for id=100 and id=200 I have to take just one extra_account - 111-1 or 111-2, as their special_values are equal.

If an account has two or more extra_accounts and their special_value are different I have to take all of them. So for id=400 and id=500, I need both - 333-1 and 333-2, as their special_values are 3 and 4.

The final result should be:

|string_agg
|text
—----
|111-1, 222-1, 333-1, 333-2

I know that I can concat values by using:

SELECT  string_agg(table.extra_account, ', ')
FROM table WHERE table.account in (111, 222, 333) and table.state = 'WORKS';

But I didn’t find a way to select all rows if special_values are different and only one row if special_values are equal.

CodePudding user response：

That's what you want per account:

SELECT account
     , CASE WHEN count(DISTINCT special_value) = 1
            THEN min(extra_account)
            ELSE string_agg(extra_account, ', ')
            END AS special_values
FROM   tbl
WHERE  account IN (111, 222, 333)
AND    state = 'WORKS'
GROUP  BY 1;

Wrap the above in a subquery and aggregate:

SELECT string_agg(special_values, ', ')
FROM (
   SELECT account
        , CASE WHEN count(DISTINCT special_value) = 1
               THEN min(extra_account)
               ELSE string_agg(extra_account, ', ')
               END AS special_values
   FROM   tbl
   WHERE  account IN (111, 222, 333)
   AND    state = 'WORKS'
   GROUP  BY 1
   ) sub;

Produces your desired result exactly.

If you really want a single extra_account per distinct special_value (unlike expressed):

SELECT string_agg(extra_account, ', ')
FROM  (SELECT DISTINCT ON (account, special_value) extra_account FROM tbl) sub;

db<>fiddle here - with an added row to show the difference

About DISTINCT ON:

• Select first row in each GROUP BY group?

(And why it's typically faster.)

CodePudding user response：

If you calculate a ROW_NUMBER then you can take 1 based on account & special_value.

SELECT 
 STRING_AGG(DISTINCT extra_account, ', ' ORDER BY extra_account) AS extra_accounts
FROM (
  SELECT account, extra_account, special_value
  , ROW_NUMBER() OVER (PARTITION BY account, special_value ORDER BY id) AS rownum
  FROM "table" t
  WHERE account IN (111, 222, 333) 
    AND state = 'WORKS' 
) q
WHERE rownum = 1;

Demo on db<>fiddle here

CodePudding user response：

I would do this via the aggregate function min on the columns account and special_value.

With CTE As (
Select account, special_value, Min(extra_account) As v
From Tbl
Where account In (111, 222, 333) And state = 'WORKS'
Group by account, special_value
)
Select string_agg(v, ',' Order by v)
From CTE

Data Output: