Determine column to insert into based on content

May 2, 2024

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

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;