Question 89815 in PostgreSQL Question posted in
The official documentation can be found here.

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
Tags:

2

Answers


  1. 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;
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search