sql to get scattered data into proper format

Database
Fan

sql to get scattered data into proper format

Hi,

My input:

c1 c2 c3 c4 c5
-- -- ------ ---- ----
id1 type1 name1 null null
id1 type2 null phone_nr1 null
id1 type3 null null Address1
id2 type1 name2 null null
id2 type2 null phone_nr2 null
id2 type3 null null Address2

Note:

null values given in the above result set can be some times empty string, spaces or random values as well.

type1 value in c2 implies c3 stores customer name
type2 value in c2 implies c4 stores customer phone number
type3 value in c2 implies c5 stores customer address

My Desired output:

col1 col2 col3 col4
-- ------ ---- ----
id1 name1 phone_nr1 Address1
id2 name2 phone_nr2 Address2

Can anybody help achieving this in a query.

Thanks.
2 REPLIES
Teradata Employee

Re: sql to get scattered data into proper format

drop table mytable;
create multiset volatile table mytable (
col1 char(3),
col2 char(5),
col3 varchar(10),
col4 varchar(10),
col5 varchar(10)
)primary index( col1)
on commit preserve rows;

insert into mytable values('id1','type1', 'name1', null, null);
insert into mytable values('id1','type2', null, 'phone_nr1', null);
insert into mytable values('id1','type3', null, null, 'Address1');
insert into mytable values('id2','type1', 'name2', null, null);
insert into mytable values('id2','type2', null, 'phone_nr2', null);
insert into mytable values('id2','type3', null, null, 'Address2');

select
col1 as Id,
max(case when col2='type1' then col3 else null end) as name_,
max(case when col2='type2' then col4 else null end) as phone,
max(case when col2='type3' then col5 else null end) as Address
from mytable
group by 1
Fan

Re: sql to get scattered data into proper format

It worked. Thanks for the super solution.