oracle - Getting multiple column values in single row -
this actual result , have used store details of client
sid attribute_values attribute_id ---------- ---------------- ----------- 20 101010 membership_no 20 allen membership_name 20 ward membership_lname 30 101011 membership_no 30 martin membership_name 30 blake membership_lname
in need retrieve details based on membership no , using sid. sid unique number . need show result
sid membership_no membership_name membership_lname ----- ------------- --------------- ---------------- 20 101010 allen ward 30 101011 martin blake
above 3 attributes constant using values client number, names inserts table. im stuck this...i need solution it.
this query have used retrieve details. returns empty
select sid, listagg(attribute_value, ',') within group (order attribute_value) att customer_attributes sid ='20' , attribute_value='101010' , attribute_id ='membership_no' , attribute_id ='membership_name' , attribute_id ='membership_lname' group sid;
you can join
. listagg
wont create 3 columns:
select a.sid, a.attribute_values membership_no, b.attribute_values membership_name, c.attribute_values membership_lname tbl join tbl b on a.sid = b.sid join tbl c on c.sid = b.sid a.attribute_id = 'membership_no' , b.attribute_id = 'membership_name' , c.attribute_id = 'membership_lname'
demo sqlfiddle
this gives same result;
select sid, max(decode(attribute_id, 'membership_no', attribute_values, '')) membership_no, max(decode(attribute_id, 'membership_name', attribute_values, '')) membership_name, max(decode(attribute_id, 'membership_lname', attribute_values, '')) membership_lname tbl group sid order sid
same can done pivot also
select * tbl pivot( max(attribute_values) attribute_id in ( 'membership_no' membership_no, 'membership_name' membership_name, 'membership_lname' membership_lname ) ) order sid
Comments
Post a Comment