Use a CASE expression without typing matched conditions manually using PostgreSQL

Question

I have a long and wide list, the following table is just an example. Table structure might look a bit horrible using SQL, but I was wondering whether there's a way to extract IDs' price using CASE expression without typing column names in order to match in the expression

IDs	A_Price	B_Price	C_Price	...
A	23			...
B		65	82	...
C				...
A	10			...
..	...	...	...	...

Table I want to achieve:

IDs	price
A	23;10
B	65
C	82
..	...

I tried:

SELECT IDs, string_agg(CASE IDs WHEN 'A' THEN A_Price
                                WHEN 'B' THEN B_Price
                                WHEN 'C' THEN C_Price
                        end::text, ';') as price
FROM table
GROUP BY IDs
ORDER BY IDs

To avoid typing A, B, A_Price, B_Price etc, I tried to format their names and call them from a subquery, but it seems that SQL cannot recognise them as columns and cannot call the corresponding values.

WITH CTE AS (
SELECT IDs, IDs||'_Price' as t FROM ID_list
)
SELECT IDs, string_agg(CASE IDs WHEN CTE.IDs THEN CTE.t
                        end::text, ';') as price
FROM table
LEFT JOIN CTE cte.IDs=table.IDs
GROUP BY IDs
ORDER BY IDs

A fiddle to work with would be very helpful. And always disclose your version of Postgres, please. — Erwin Brandstetter
– Erwin Brandstetter, Commented Feb 9, 2023 at 0:24

Erwin Brandstetter · Accepted Answer · 2023-02-09 00:42:34Z

1

You can use a document type like json or hstore as stepping stone:

Basic query:

SELECT t.ids
     , to_json(t.*) ->> (t.ids || '_price') AS price
FROM   tbl t;

to_json() converts the whole row to a JSON object, which you can then pick a (dynamically concatenated) key from.

Your aggregation:

SELECT t.ids
     , string_agg(to_json(t.*) ->> (t.ids || '_price'), ';') AS prices
FROM   tbl t
GROUP  BY 1
ORDER  BY 1;

Converting the whole (big?) row adds some overhead, but you have to read the whole table for your query anyway.

edited Feb 9, 2023 at 0:42

answered Feb 9, 2023 at 0:34

Erwin Brandstetter

668k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Biegeleisen · Accepted Answer · 2023-02-08 23:24:04Z

0

A union would be one approach here:

SELECT IDs, A_Price FROM yourTable WHERE A_Price IS NOT NULL
UNION ALL
SELECT IDs, B_Price FROM yourTable WHERE B_Price IS NOT NULL
UNION ALL
SELECT IDs, C_Price FROM yourTable WHERE C_Price IS NOT NULL;

answered Feb 8, 2023 at 23:24

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

1 Comment

M Shen Over a year ago

Thank you. In this case I still have to type A_price, B_price, C_price manually. since I have a very wide list, it would not be efficient.

Collectives™ on Stack Overflow

Use a CASE expression without typing matched conditions manually using PostgreSQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related