normalizing data

[smuthcrmnl777]

Is there a way to take this query and normalize it? I have approximately 15 elements that are tested and display a result along with 15 lower specs and 15 upper specs. I would like to take the results and pile them on top of each other therefore creating a Results, Lower Spec and Upper Spec column. Can someone help me on this?

SELECT     dbo.vM_RD_FR_SpecList.Edono, dbo.tM_RD_OrdersReleased_NotCertified.WONO, dbo.vM_RD_FR_SpecList.[Nominal Diameter], 
                      dbo.vM_RD_FR_SpecList.[Product Name], dbo.vM_RD_FR_SpecList.ProdName, dbo.vM_RD_QA_Qvac.C2, dbo.vM_RD_QA_Qvac.S, 
                      dbo.vM_RD_QA_Qvac.P1, dbo.vM_RD_QA_Qvac.SI1, dbo.vM_RD_QA_Qvac.MN3, dbo.vM_RD_QA_Qvac.CU5 + dbo.vM_RD_QA_Xray.CU AS CU_Total, 
                      dbo.vM_RD_QA_Qvac.MO2, dbo.vM_RD_QA_Qvac.CR1, dbo.vM_RD_QA_Qvac.NI2, dbo.vM_RD_QA_Qvac.TI4, dbo.vM_RD_QA_Qvac.AL7, 
                      dbo.vM_RD_QA_Qvac.V3, dbo.vM_RD_QA_Qvac.NB, dbo.vM_RD_QA_Qvac.ZR2, dbo.vM_RD_QA_Qvac.AS1, dbo.vM_RD_QA_Qvac.SN2, 
                      dbo.vM_RD_QA_Qvac.SB2, CASE WHEN dbo.tM_RD_Q2_Specs.[C_LSPEC] IS NULL 
                      THEN 'NO SPEC' ELSE dbo.tM_RD_Q2_Specs.[C_LSPEC] END AS CLS, CASE WHEN dbo.tM_RD_Q2_Specs.[C_USPEC] IS NULL 
                      THEN 'NO SPEC' ELSE dbo.tM_RD_Q2_Specs.[C_USPEC] END AS CUS, CASE WHEN dbo.tM_RD_Q2_Specs.[S_LSPEC] IS NULL 
                      THEN 'NO SPEC' ELSE dbo.tM_RD_Q2_Specs.[S_LSPEC] END AS SLS, CASE WHEN dbo.tM_RD_Q2_Specs.[S_USPEC] IS NULL 
                      THEN 'NO SPEC' ELSE dbo.tM_RD_Q2_Specs.[S_USPEC] END AS SUS, CASE WHEN dbo.tM_RD_Q2_Specs.[P_LSPEC] IS NULL 
                      THEN 'NO SPEC' ELSE dbo.tM_RD_Q2_Specs.[P_LSPEC] END AS PLS, CASE WHEN dbo.tM_RD_Q2_Specs.[P_USPEC] IS NULL 
                      THEN 'NO SPEC' ELSE dbo.tM_RD_Q2_Specs.[P_USPEC] END AS PUS, CASE WHEN dbo.tM_RD_Q2_Specs.[SI_LSPEC] IS NULL 
                      THEN 'NO SPEC' ELSE dbo.tM_RD_Q2_Specs.[SI_LSPEC] END AS SILS, CASE WHEN dbo.tM_RD_Q2_Specs.[SI_USPEC] IS NULL 
                      THEN 'NO SPEC' ELSE dbo.tM_RD_Q2_Specs.[SI_USPEC] END AS SIUS, CASE WHEN dbo.tM_RD_Q2_Specs.[MN_LSPEC] IS NULL 
                      THEN 'NO SPEC' ELSE dbo.tM_RD_Q2_Specs.[MN_LSPEC] END AS MNLS, CASE WHEN dbo.tM_RD_Q2_Specs.[MN_USPEC] IS NULL 
                      THEN 'NO SPEC' ELSE dbo.tM_RD_Q2_Specs.[MN_USPEC] END AS MNUS, CASE WHEN dbo.tM_RD_Q2_Specs.[CUTOTAL_LSPEC] IS NULL 
                      THEN 'NO SPEC' ELSE dbo.tM_RD_Q2_Specs.[CUTOTAL_LSPEC] END AS CUTOTALLS, CASE WHEN dbo.tM_RD_Q2_Specs.[CUTOTAL_USPEC] IS NULL
                       THEN 'NO SPEC' ELSE dbo.tM_RD_Q2_Specs.[CUTOTAL_USPEC] END AS CUTOTALUS, CASE WHEN dbo.tM_RD_Q2_Specs.[MO_LSPEC] IS NULL 
                      THEN 'NO SPEC' ELSE dbo.tM_RD_Q2_Specs.[MO_LSPEC] END AS MOLS, CASE WHEN dbo.tM_RD_Q2_Specs.[MO_USPEC] IS NULL 
                      THEN 'NO SPEC' ELSE dbo.tM_RD_Q2_Specs.[MO_USPEC] END AS MOUS, CASE WHEN dbo.tM_RD_Q2_Specs.[CR_LSPEC] IS NULL 
                      THEN 'NO SPEC' ELSE dbo.tM_RD_Q2_Specs.[CR_LSPEC] END AS CRLS, CASE WHEN dbo.tM_RD_Q2_Specs.[CR_USPEC] IS NULL 
                      THEN 'NO SPEC' ELSE dbo.tM_RD_Q2_Specs.[CR_USPEC] END AS CRUS, CASE WHEN dbo.tM_RD_Q2_Specs.[NI_LSPEC] IS NULL 
                      THEN 'NO SPEC' ELSE dbo.tM_RD_Q2_Specs.[NI_LSPEC] END AS NILS, CASE WHEN dbo.tM_RD_Q2_Specs.[NI_USPEC] IS NULL 
                      THEN 'NO SPEC' ELSE dbo.tM_RD_Q2_Specs.[NI_USPEC] END AS NIUS, CASE WHEN dbo.tM_RD_Q2_Specs.[TI_LSPEC] IS NULL 
                      THEN 'NO SPEC' ELSE dbo.tM_RD_Q2_Specs.[TI_LSPEC] END AS TILS, CASE WHEN dbo.tM_RD_Q2_Specs.[TI_USPEC] IS NULL 
                      THEN 'NO SPEC' ELSE dbo.tM_RD_Q2_Specs.[TI_USPEC] END AS TIUS, CASE WHEN dbo.tM_RD_Q2_Specs.[AL_LSPEC] IS NULL 
                      THEN 'NO SPEC' ELSE dbo.tM_RD_Q2_Specs.[AL_LSPEC] END AS ALLS, CASE WHEN dbo.tM_RD_Q2_Specs.[AL_USPEC] IS NULL 
                      THEN 'NO SPEC' ELSE dbo.tM_RD_Q2_Specs.[AL_USPEC] END AS ALUS, CASE WHEN dbo.tM_RD_Q2_Specs.[V_LSPEC] IS NULL 
                      THEN 'NO SPEC' ELSE dbo.tM_RD_Q2_Specs.[V_LSPEC] END AS VLS, CASE WHEN dbo.tM_RD_Q2_Specs.[V_USPEC] IS NULL 
                      THEN 'NO SPEC' ELSE dbo.tM_RD_Q2_Specs.[V_USPEC] END AS VUS, CASE WHEN dbo.tM_RD_Q2_Specs.[NB_LSPEC] IS NULL 
                      THEN 'NO SPEC' ELSE dbo.tM_RD_Q2_Specs.[NB_LSPEC] END AS NBLS, CASE WHEN dbo.tM_RD_Q2_Specs.[NB_USPEC] IS NULL 
                      THEN 'NO SPEC' ELSE dbo.tM_RD_Q2_Specs.[NB_USPEC] END AS NBUS, CASE WHEN dbo.tM_RD_Q2_Specs.[ZR_LSPEC] IS NULL 
                      THEN 'NO SPEC' ELSE dbo.tM_RD_Q2_Specs.[ZR_LSPEC] END AS ZRLS, CASE WHEN dbo.tM_RD_Q2_Specs.[ZR_USPEC] IS NULL 
                      THEN 'NO SPEC' ELSE dbo.tM_RD_Q2_Specs.[ZR_USPEC] END AS ZRUS, CASE WHEN dbo.tM_RD_Q2_Specs.[AS_LSPEC] IS NULL 
                      THEN 'NO SPEC' ELSE dbo.tM_RD_Q2_Specs.[AS_LSPEC] END AS ASLS, CASE WHEN dbo.tM_RD_Q2_Specs.[AS_USPEC] IS NULL 
                      THEN 'NO SPEC' ELSE dbo.tM_RD_Q2_Specs.[AS_USPEC] END AS ASUS, CASE WHEN dbo.tM_RD_Q2_Specs.[SN_LSPEC] IS NULL 
                      THEN 'NO SPEC' ELSE dbo.tM_RD_Q2_Specs.[SN_LSPEC] END AS SNLS, CASE WHEN dbo.tM_RD_Q2_Specs.[SN_USPEC] IS NULL 
                      THEN 'NO SPEC' ELSE dbo.tM_RD_Q2_Specs.[SN_USPEC] END AS SNUS, CASE WHEN dbo.tM_RD_Q2_Specs.[SB_LSPEC] IS NULL 
                      THEN 'NO SPEC' ELSE dbo.tM_RD_Q2_Specs.[SB_LSPEC] END AS SBLS, CASE WHEN dbo.tM_RD_Q2_Specs.[SB_USPEC] IS NULL 
                      THEN 'NO SPEC' ELSE dbo.tM_RD_Q2_Specs.[SB_USPEC] END AS SBUS
FROM         dbo.tM_RD_OrdersReleased_NotCertified INNER JOIN
                      dbo.vM_RD_QA_Qvac ON dbo.tM_RD_OrdersReleased_NotCertified.WONO = dbo.vM_RD_QA_Qvac.WONO INNER JOIN
                      dbo.vM_RD_FR_SpecList ON dbo.tM_RD_OrdersReleased_NotCertified.PRODNO = dbo.vM_RD_FR_SpecList.Edono INNER JOIN
                      dbo.tM_RD_Q2_Specs ON dbo.vM_RD_FR_SpecList.ProdName = dbo.tM_RD_Q2_Specs.[Lincoln Name] LEFT OUTER JOIN
                      dbo.vM_RD_QA_Xray ON dbo.tM_RD_OrdersReleased_NotCertified.WONO = dbo.vM_RD_QA_Xray.SID3

 

[TJRTech]

First of all, go to SQL Books Online and take a look at ISNULL(). 80% of the query is replacing stock column values with 'NO SPEC' via CASE statements when NULL, and that is exactly what ISNULL is good for.

You may feel better about the query after doing that simple bit of housecleaning...and you will have learned a new trick.

 

[chrissie1]

Actually, getting the table to not accept nulls would be even better.

Christiaan Baes
Belgium

"My new site" - Me

 

[TJRTech]

Chrissie1, sometimes NULL is an acceptable input value for a table...for example, in those cases where NO VALUE is possible and you want to allow for that.

 

[chrissie1]

Perhaps but in his case it is very clear that NULL means "no spec" so he should set no spec as the default since that is what null means. Anyhow the OP clearly knows best and refuses to comment any further.

Christiaan Baes
Belgium

"My new site" - Me