Here I Have one ID having different Codes, I have to display if the Id has a Code value then need to display the code value row else null value row.

CodePudding user response：

These all go with the assumption that a maximum two rows can be present and that one of them will have a null Code when there are two.

with data as (select *, count(*) over (partition by ID) as cnt from T)
select ID, Code, Value from data
where cnt = 1 or Code is not null;

or

select distinct t1.ID,
    coalesce(t2.Code, t1.Code) as Code, coalesce(t2.Value, t1.Value) as Value
from T t1 left outer join T t2 on t2.ID = t1.ID and t2.Code is not null;

or

with data as (select *, max(Code) over (partition by ID) as maxCode from T)
select ID, Code, Value from data
where coalesce(Code, '!@#') = coalesce(maxCode, '!@#');

or

with data as (
    select *,
        row_number() over (
            partition by ID
            order by case when Code is not null then 0 else 1 end) as rn
    from T
) select ID, Code, Value from data where rn = 1;

or

with data as (
    select distinct ID,
        max(Code) over (partition by ID) as Code,
        first_value(Value) over (
            partition by ID
            order by case when Code is not null then 0 else 1 end) as Value
    from T
) select * from data;

or

select * from T t1
where Code is not null or not exists
    (select 1 from T t2 where t2.ID = t1.ID and t2.Code is not null);

or

select ID, max(Code) as Code,
    /* works better with character values */
    substring(max(left(coalesce(Code, '')   '          ', 10)   Value), 10, 50)
from T group by ID

CodePudding user response：

Well this worked for me SELECT * FROM tablename