Last active
June 23, 2020 14:36
-
-
Save Osiyuks/d22af688335b99cc51f7972e010d4fdb to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| with t as ( | |
| select 1 id, 'name=Alex; age=30; project=project1; company = MP' Data | |
| union all | |
| select 2, 'name=Vin; age=31; project=project3; company = AC' | |
| union all | |
| select 3, 'name=Sara; age=32; company = FF; hobby= cricket' | |
| ), g as ( | |
| Select id, split(flatdata,'=')[ORDINAL(1)] Key, split(flatdata,'=')[ORDINAL(2)] Value | |
| from | |
| (select id,split(Data,'; ') KeyValue from t ) | |
| left join unnest(KeyValue) flatdata | |
| ) | |
| select id | |
| ,MAX(IF(Key = 'name', Value, NULL)) as name | |
| ,MAX(IF(Key = 'age', Value, NULL)) as age | |
| ,MAX(IF(Key = 'project', Value, NULL)) as project | |
| ,MAX(IF(Key = 'company ', Value, NULL)) as company | |
| ,MAX(IF(Key = 'hobby', Value, NULL)) as hobby | |
| from g | |
| group by 1 order by 1 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
with t as (
select 1 id, 'name=Alex; age=30; project=project1; company = MP' Data
union all
select 2, 'name=Vin; age=31; project=project3; company = AC'
union all
select 3, 'name=Sara; age=32; company = FF; hobby= cricket'
), g as (
Select id, split(flatdata,'=')[ORDINAL(1)] Key, split(flatdata,'=')[ORDINAL(2)] Value
from
(select id,split(Data,'; ') KeyValue from t )
left join unnest(KeyValue) flatdata
)
select id
,MAX(IF(Key = 'name', Value, NULL)) as name
,MAX(IF(Key = 'age', Value, NULL)) as age
,MAX(IF(Key = 'project', Value, NULL)) as project
,MAX(IF(Key = 'company ', Value, NULL)) as company
,MAX(IF(Key = 'hobby', Value, NULL)) as hobby
from g
group by 1 order by 1