following is my data set and table structure
create table students (
student_id varchar(255),
contact_numbers super
)
student_id, contact_numbers
1 {"mobile":111,"office":222}
2 {"mobile":4444}
3 {"mobile":555}
5 {"mobile":8888}
6 {}
7 {}
8 {}
9 {"office":222}
i want to unpivot contact numbers, but when unpivoting blank values will be remove from the result.
unpivote query
SELECT student_id, attr, val FROM students tb,
UNPIVOT tb.contact_numbers AS val AT attr
result
student_id, attr, val
1 mobile 111
1 office 222
2 mobile 4444
3 mobile 555
5 mobile 8888
9 office 222
you can see 6, 7 and 8 student ids are missing from the result. i want to get all student ids from the result
when i run the below query with the left join, student ids are missing from the result. i need those student ids and null or blank values
select student_id, attr, val from students tb
LEFT JOIN tb.contact_numbers contact ON true,
UNPIVOT tb.contact_numbers AS val AT attr
expected result
student_id, attr, val
1 mobile 111
1 office 222
2 mobile 4444
3 mobile 555
5 mobile 8888
6
7
8
9 office 222
The issue is that the values attributes don't exist for these students. So there is nothing unpivot. There are a number of ways to attack this but you will likely want to LEFT join student id to this info. This way you know all student id exist in the output.
You could also pre-condition your data, or UNION and group by. The bottom line is you cannot unpivot with what doesn't exist.
UPDATE:
Left joining to add the full list of student_id values will look like (untested):
SELECT b.student_id, a.attr, a.val
FROM students b
LEFT JOIN (
SELECT student_id, attr, val
FROM students tb,
UNPIVOT tb.contact_numbers AS val AT attr ) a
ON a.student_id = b.student_id
ORDER BY b.student_id;