I have a query that's joined to other tables. I am pivoting the data based on result_value and units. These two items are related to each other.
E.g. Cu measurement = ppm , Cu value = 27
However there are fields in the column that are null. How do I still print its measurement irrespective if its associated column is null. The fields represented in the Unit columns are all the same
Sample ID Lab ID N_Val NUnit P_Val PUnit
1 CU ANNGRO L19042090 1.60 1598 %
2 CU ANNGRO L19042090
3 CU BREAK THRU L19042091 1.53 % 1598 %
What I have tried:
-- Simplify the pivot selection query by separating the query using a with clause
WITH pivot_data AS(
SELECT va.identity,vc.units,
s.field_name "Sample ID",
s.id_text "Lab ID",
TO_CHAR(str.result_value, S_FORMATMASK_PACKAGE.s_FormatMask(vc.analysis, s.id_numeric))result_value
from samp_test_result str
inner join sample s on str.id_numeric = s.id_numeric and str.id_text = s.id_text
inner join client c on c.id = s.client_id
inner join versioned_analysis va on va.identity = str.analysis
inner join versioned_component vc on vc.analysis = va.identity and vc.analysis_version = va.analysis_version and vc.name = str.component_name
WHERE s.fas_sample_type = 'LEAF'
AND s.status = 'A'
AND s.flg_released = 'T'
AND vc.flg_report = 'T'
AND c.id = UPPER ('N000068')
AND s.ID_NUMERIC between TO_NUMBER(12918) and TO_NUMBER(12920)
)
SELECT pvt12.*
FROM(SELECT * FROM pivot_data PIVOT ( MAX(result_value) result_value , MAX(units) units FOR identity IN(
'NIR_N' "Nitro",
'XRF_P' "P",
'XRF_CA' "Ca",
'XRF_MG' "Mg",
'XRF_MN' "Mn",
'XRF_S' "S",
'XRF_ZN' "Zn",
'XRF_CU' "Cu",
'XRF_FE' "Fe",
'XRF_K' "K",
'XRF_SI' "Si"))) pvt12