CODEWITHSUNDEEP
postgresql
Rajeev kumar
Rajeev kumar

Reputation: 163

SQL query to Break on row into multiple row based on some delimiter

I have a table named `test' which has following structure.

category        key                                                 value    
name           real_name:Brad,nick_name:Brady,name_type:small        NOVALUE 
other          description                                           cool

But I want to break key column into multiple rows based on , delimiter and value after : delimiter should be a part of value column where value is equal to NOVALUE. So output should look like:

category        key               value
name           real_name          Brad
name           nick_name          Brady
name           name_type          small
other          description        cool

How to write sql query for this . I am using postgresql.

Any help ? Thanks in advance.

Upvotes: 1

Views: 625

Answers (2)

Rajeev kumar
Rajeev kumar

Reputation: 163

 select category, 
       split_part(key_value, ':', 1) as key,
       case  when  value = 'NOVALUE' then split_part(key_value, ':', 2) else value end 
from test
  cross join lateral unnest(string_to_array(key, ',')) as t (key_value)

Upvotes: 0

user330315
user330315

Reputation:

You can use string_to_array and unnest to do this:

select ts.category, 
       split_part(key_value, ':', 1) as key,
       split_part(key_value, ':', 2) as value
from test ts
  cross join lateral unnest(string_to_array(ts.key, ',')) as t (key_value)
where ts.value = 'NOVALUE'

union all

select category, 
       key, 
       value
from test
where value <> 'NOVALUE';

SQLFiddle example: http://sqlfiddle.com/#!15/6f1e6/1

Upvotes: 1

Related Questions