Comment renvoyer un enregistrement si toutes les colonnes renvoient nulles ou ne renvoient que des colonnes non nulles

Utilisez simplement UNION , comme ci-dessous:

select ID,Location,NameA,ValueA from table where NameA is not null
union
select ID,Location,NameB,ValueB from table where NameB is not null
union
select ID,Location,NameC,ValueC from table where NameC is not null
union
select ID,Location,NameD,ValueD from table where NameD is not null
union
select ID,Location,null,null from table where coalesce(NameA,NameB,NameC,NameD) is null

Ou vous pouvez utiliser UNPIVOT comme suit:

-- Sample Data:
WITH dat(ID,Location,NameA,ValueA,NameB,ValueB,NameC,ValueC,NameD,ValueD) AS
  (SELECT 0 ,   'BL'   ,NULL ,NULL  ,NULL ,NULL,NULL ,NULL,NULL ,NULL  FROM dual
    UNION
   SELECT 1 ,   'GT'   ,'x'  ,'y'   ,NULL ,NULL,'s'  , 'z',NULL ,NULL  FROM dual
    UNION
   SELECT 2 ,   'SZ'   ,'c'  ,'d'   ,'e'  ,'f' ,NULL ,NULL,NULL ,NULL  FROM dual)
-- SQL-Statement:
SELECT id,location, NAME, VALUE 
  FROM (
    SELECT id,location, NAME, VALUE,sk, MAX(NAME) over (PARTITION BY id) max_name
      FROM dat
      UNPIVOT INCLUDE NULLS(
        (NAME, VALUE) FOR sk IN ((NameA, ValueA) AS 1
                                ,(NameB, ValueB) AS 2
                                ,(NameC, ValueC) AS 3
                                ,(NameD, ValueD) AS 4)
      )
)
WHERE (SK = 1 AND max_name IS NULL)
   OR NAME IS NOT NULL

declare @tempTbl table ([ID] int, [Location] nvarchar(10), [Name] nvarchar(10), [Value] nvarchar(10));
insert into  @tempTbl 
 select  [ID],[Location],[Name], [Value] From
(
SELECT [ID],[Location],[NameA] as [Name],[ValueA] as [Value] FROM [dbo].[LocationTbl]
    UNION 
    SELECT [ID],[Location],[NameB],[ValueB] FROM [dbo].[LocationTbl]
    UNION 
    SELECT [ID],[Location],[NameC],[ValueC] FROM [dbo].[LocationTbl]
    UNION 
    SELECT [ID],[Location],[NameD],[ValueD] FROM [dbo].[LocationTbl]
) as X
where [Name] is not null AND [Value] is not null
select * from @tempTbl
UNION
SELECT [ID], [Location], Null as [Name], Null as [Value]
FROM [dbo].[LocationTbl]
WHERE  [Location] not in (select [Location] from @tempTbl)
order by [ID];
try this.

La réponse la plus simple dans Oracle est probablement:

select t.id, v.name, v.value
from t cross apply
     (values ('a', t.namea, t.valuea),
             ('b', t.nameb, t.valueb),
             ('c', t.namec, t.valuec),
             ('d', t.named, t.valued)
     ) v(which, name, value)
where v.name is not null or
      (v.which = 'a' and
       t.namea is null and t.nameb is null and t.namec is null and t.named is null
      );

Pour certains (raison probablement absurde), j'ai fourni la réponse ci-dessous en pensant que la question était étiquetée SQL Server. Oracle 12C prend en charge les jointures latérales, donc ce qui suit pourrait être adapté à la syntaxe Oracle.

Je laisse le reste de la réponse ici.

select id, name, value
from (select t.id, v.name, v.value,
             count(*) over (partition by t.id, v.name, v.value) as cnt_nv,
             row_number() over (order by t.id) as seqnum
      from t cross apply
           (values (t.namea, t.valuea), (t.nameb, t.valueb), (t.namec, t.valuec), (t.named, t.valued)
           ) v(name, value)
     ) v
where name is not null or
      (cnt_nv = 4 and seqnum = 1);

I aime décomposer en utilisant cross apply . Donc, le décompressé de base est:

select t.id, v.name, v.value
from t cross apply
     (values (t.namea, t.valuea), (t.nameb, t.valueb), (t.namec, t.valuec), (t.named, t.valued)
     ) v(name, value);

Vous voulez alors une condition que null n'apparaisse qu'une seule fois si c'est tout le valeurs. Vous pouvez utiliser les fonctions de fenêtre pour cela:

select t.id, v.name, v.value
from t cross apply
     (values ('a', t.namea, t.valuea),
             ('b', t.nameb, t.valueb),
             ('c', t.namec, t.valuec),
             ('d', t.named, t.valued)
     ) v(which, name, value)
where v.name is not null or
      (v.which = 'a' and

      );

Je ne sais pas si vous vous souciez simplement de savoir si nom est NULL code > ou si vous voulez que nom et valeur soient NULL . L'une ou l'autre logique peut être mise en œuvre; on ne sait tout simplement pas ce que vous voulez.

EDIT:

Vous pouvez également le faire sans sous-requête:

select t.id, t.namea as name. t.valuea as valuea
from t
where t.namea is not null or 
      (t.namea is null and t.nameb is null and t.namec is null and t.named is null)
union all
select t.id, t.nameb as name. t.valueb as valuea
from t
where t.nameb is not null 
union all
select t.id, t.namec as name. t.valuec as valuea
from t
where t.namec is not null 
union all
select t.id, t.named as name. t.valued as valuea
from t
where t.named is not null ;

Ceci est probablement la manière la plus simple d'écrire la logique.