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

Postgresql – Determine column to insert into based on content

Imagine I have rows of data in a postgres table, such as:

Id Value
1 User_1
2 Region_3
3 User_2

And I want to select out values in a way that uses the part of Value before the _ character to determine which column to populate, such that the results would be:

Id User Region
1 1 null
2 null 3
3 2 null

There would only need to be a fixed number of values that would only ever be small. Is there a way to do this in SQL?

I can see that it’s possible to populate cells with different values such as in this answer, but I have not been able to find any details on populating a column based on the content of a row.

I’ve been able to get the relevant part of the value out using left:

SELECT
left("Value", strpos("Value", '_') - 1) as "Type"

But I cannot see how I would take this and use it determining the column.

Tags:

2

Answers


  1. 0

    use the case statement.

    here is the fiddle https://www.db-fiddle.com/f/nTapovF5r5b8U4Tmjhy4tU/1

    select id, 
case  split_part(value, '_', 1)
    when 'User' then split_part(value, '_', 2)
    end as User,
 case  split_part(value, '_', 1)
    when 'Region' then split_part(value, '_', 2)
    end as Region
from tableone;
    Login or Signup to reply.
  2. 0

    For more columns, split the string once in a subquery, and add a cast once (if all share the same target type), and use a CASE expression per column in the outer SELECT:

    SELECT id
     , CASE WHEN key = 'User'   THEN value END AS "User"
     , CASE WHEN key = 'Region' THEN value END AS "Region"
FROM  (
   SELECT id
        , split_part(value, '_', 1) AS key
        , split_part(value, '_', 2)::int AS value  -- cast?
   FROM   tbl
   ) sub;

    fiddle

    Related:

    For more than a few target columns, or to combine multiple values with the same id value in a single row (not the case in your sample values!), consider a crosstab() query:

    SELECT *
FROM   crosstab(
   $$
   SELECT id
        , split_part(value, '_', 1) AS key
        , split_part(value, '_', 2) AS value
   FROM   tbl
-- ORDER  BY id  -- not needed for single value per target row (?)
   $$

 , $$VALUES ('User'::text), ('Region')$$        -- more?
   ) AS ct (id int, "User" int, "Region" int);  -- more?

    fiddle

    This also casts value columns from text to int implicitly.

    See:

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

Back To Top
Search