isnull pivot server sql_Sql pivot - 无论空值如何都可以透视数据

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 sameSample IDLab ID N_ValNUnit P_Val PUnit

1CU ANNGROL19042090 1.60 1598 %

2CU ANNGROL19042090

3CU BREAK THRUL19042091 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

解决方案I am a SQL Server person and I know what values I would replace the nulls with and I would be using the IsNull function.... But this is not Sql Server and I have no clue where your nulls are going to pop in and what values you want to replace them with.

Perhaps this will help

Ask TOM "Pivot and null values"[^]