CODEWITHSUNDEEP
sqlpostgresqlcase
M Shen
M Shen

Reputation: 61

Use a CASE expression without typing matched conditions manually using PostgreSQL

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

Upvotes: 1

Views: 88

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656586

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.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

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;

Upvotes: 0

Related Questions