sqlpostgresql

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.

Solution

  • 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;