I have a table MVKE where each Article_# is assigned a VKORG (Up to 4).

I want to display the data as follows, where each VKORG value (up to 4) gets assigned a Yes if it exists for the article_#, a null for not existing:

I've used the below code to do this (also joining with a master table) but the issue I am having is that when the article have all 4 VKORG (0001, 0002, 0003,0004) it does not show up on the result however having any missing VKORG will show up on the report (i.e. article_# 12346). I can't figure out what the issue is that is preventing records having all 4 VKORG showing up (How do I get the 1st line to show up in the table above for article # 12345)

SELECT

SUBSTRING(mara.MATNR, PATINDEX('%[^0]%', mara.MATNR), LEN(mara.MATNR)) as ARTICLE_#,
makt.MAKTG as SAP_ARTICLE_DESCR,
mara.MATKL as MCH0,
MAX(CASE WHEN VKORG = '0001' THEN 'YES' end) as MVKE_1,
MAX(CASE WHEN VKORG = '0002' THEN 'YES' end) as MVKE_2,
MAX(CASE WHEN VKORG = '0003' THEN 'YES' end) as MVKE_3,
MAX(CASE WHEN VKORG = '0004' THEN 'YES' end) as MVKE_4

FROM Dbo.MARA as mara
  inner join Dbo.makt as makt on makt.matnr = mara.MATNR and makt.SPRAS = 'E'
  inner join Dbo.mvke as mvke on mvke.MATNR = mara.MATNR and mvke.VTWEG = '34' and mvke.vmsta in ('A1', '', 'P2') 

  group by SUBSTRING(mara.MATNR, PATINDEX('%[^0]%', mara.MATNR), LEN(mara.MATNR)),
makt.MAKTG,
mara.MATKL

having 

max(case when VKORG = '0001'  then 'YES' END) is null OR
max(case when VKORG = '0002'  then 'YES' END) is null OR
max(case when VKORG = '0003'  then 'YES' END) is null OR
max(case when VKORG = '0004'  then 'YES' END) is null

CodePudding user response：

How about a simple PIVOT

Example

Select *
 From  (
        Select [Article_#]
              ,Col = concat('MVKE_',try_convert(int,[VKORG]))
              ,Val = 'Yes'
        from YourTable
       ) src
 Pivot (max(Val) for Col in ([MVKE_1],[MVKE_2],[MVKE_3],[MVKE_4]) ) pvt

Results

Article_#   MVKE_1  MVKE_2  MVKE_3  MVKE_4
12345       Yes     Yes     Yes     Yes
12346       Yes     NULL    Yes     Yes