Geek asks and answers

How to get the first column that is not null

Sorry if the title is bad. But I do not know how to solve this problem. I am really struggling with a query in sql. I do not know if this question has been asked before. But I need to get the first column which is not null. I have a table like this:

ID  0       1       2       3       4       5       6       7       8      9    
59  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL   text1
185 NULL    NULL    NULL    NULL    NULL    text1   text2   text3   text4  text5
428 NULL    NULL    NULL    NULL    NULL    NULL    text1   text2   text3  text4
53  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    text1  text2
452 NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL   text1
267 text1   text2   text3   text4   text5   text6   text7   text8   text9  text10

And I would like the result to be like this:

ID  0       1       2       3       4       5       6       7       8      9    
59  text1   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL   NULL
185 text1   text2   text3   text4   text5   NULL    NULL    NULL    NULL   NULL
428 text1   text2   text3   text4   NULL    NULL    NULL    NULL    NULL   NULL
53  text1   text2   NULL    NULL    NULL    NULL    NULL    NULL    NULL   NULL
452 text1   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL   NULL
267 text1   text2   text3   text4   text5   text6   text7   text8   text9  text10

The column IDis unique for each row.

My unsuccessful attempt was to create a scaled function and run it for each column. The function is as follows:

CREATE FUNCTION GetCulumnsThatIsNotNull
(
    @column1 VARCHAR(500),
    @column2 VARCHAR(500),
    @column3 VARCHAR(500),
    @column4 VARCHAR(500),
    @column5 VARCHAR(500),
    @column6 VARCHAR(500),
    @column7 VARCHAR(500),
    @column8 VARCHAR(500),
    @column9 VARCHAR(500),
    @column10 VARCHAR(500)
)
RETURNS VARCHAR(500)
AS
BEGIN
RETURN 
    COALESCE(@column1,
    COALESCE(@column2,
    COALESCE(@column3,
    COALESCE(@column4,
    COALESCE(@column5,
    COALESCE(@column6,
    COALESCE(@column8,
    COALESCE(@column9,
    COALESCE(@column10,null)))))))))
END

But the problem with this function is that it just selects the first column null. Thus, the result will look like this:

ID  0       1       2       3       4       5       6       7       8      9    
59  text1   text1   text1   text1   text1   text1   text1   text1   text1  text1
185 text1   text1   text1   text1   text1   text1   text1   text1   text1  text1
428 text1   text1   text1   text1   text1   text1   text1   text1   text1  text1
53  text1   text1   text1   text1   text1   text1   text1   text1   text1  text1
452 text1   text1   text1   text1   text1   text1   text1   text1   text1  text1
267 text1   text1   text1   text1   text1   text1   text1   text1   text1  text1

Any suggestions?

EDIT

This will not work broadcast.

COALESCE(pvt.[0],
pvt.[1],
pvt.[2],
pvt.[3],
pvt.[4],
pvt.[5],
pvt.[6],
pvt.[7],
pvt.[8],
pvt.[9])

+3
2

CTE - , . , - , .

-- Setup test data
declare @texts table (
      ID int not null primary key
    , Col0 varchar(10) null
    , Col1 varchar(10) null
    , Col2 varchar(10) null
    , Col3 varchar(10) null
    , Col4 varchar(10) null
    , Col5 varchar(10) null
    , Col6 varchar(10) null
    , Col7 varchar(10) null
    , Col8 varchar(10) null
    , Col9 varchar(10) null
)
insert into @texts select 59, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'text1'
insert into @texts select 185, NULL, NULL, NULL, NULL, NULL, 'text1', 'text2', 'text3', 'text4', 'text5'
insert into @texts select 428, NULL, NULL, NULL, NULL, NULL, NULL, 'text1', 'text2', 'text3', 'text4'
insert into @texts select 53, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'text1', 'text2'
insert into @texts select 452, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'text1'
insert into @texts select 267, 'text1', 'text2', 'text3', 'text4', 'text5', 'text6', 'text7', 'text8', 'text9', 'text10'

-- Normalize and get new column in a CTE
;with cte as (
    select *
        , row_number() over (partition by ID order by Col) - 1 as NewCol
    from (
        select ID, 0 as Col
            , (select Col0 from @texts where ID = a.ID) as Val
        from @texts a
        union all
        select ID, 1 as Col
            , (select Col1 from @texts where ID = a.ID) as Val
        from @texts a
        union all
        select ID, 2 as Col
            , (select Col2 from @texts where ID = a.ID) as Val
        from @texts a
        union all
        select ID, 3 as Col
            , (select Col3 from @texts where ID = a.ID) as Val
        from @texts a
        union all
        select ID, 4 as Col
            , (select Col4 from @texts where ID = a.ID) as Val
        from @texts a
        union all
        select ID, 5 as Col
            , (select Col5 from @texts where ID = a.ID) as Val
        from @texts a
        union all
        select ID, 6 as Col
            , (select Col6 from @texts where ID = a.ID) as Val
        from @texts a
        union all
        select ID, 7 as Col
            , (select Col7 from @texts where ID = a.ID) as Val
        from @texts a
        union all
        select ID, 8 as Col
            , (select Col8 from @texts where ID = a.ID) as Val
        from @texts a
        union all
        select ID, 9 as Col
            , (select Col9 from @texts where ID = a.ID) as Val
        from @texts a
    ) as b
    where b.Val is not null
)
-- Run a pivot of the CTE
select ID
    , (select Val from cte where ID = a.ID and NewCol = 0) as Col0
    , (select Val from cte where ID = a.ID and NewCol = 1) as Col1
    , (select Val from cte where ID = a.ID and NewCol = 2) as Col2
    , (select Val from cte where ID = a.ID and NewCol = 3) as Col3
    , (select Val from cte where ID = a.ID and NewCol = 4) as Col4
    , (select Val from cte where ID = a.ID and NewCol = 5) as Col5
    , (select Val from cte where ID = a.ID and NewCol = 6) as Col6
    , (select Val from cte where ID = a.ID and NewCol = 7) as Col7
    , (select Val from cte where ID = a.ID and NewCol = 8) as Col8
    , (select Val from cte where ID = a.ID and NewCol = 9) as Col9
from cte a
group by ID
order by ID
+3
select *
from
  (
    select T.ID, C.C, row_number() over(partition by T.ID order by C.N) - 1 as rn
    from @T as T
      cross apply (values (C0, 0),(C1, 1),(C2, 2),(C3, 3),(C4, 4),
                          (C5, 5),(C6, 6),(C7, 7),(C8, 8),(C9, 9)) as C(C, N)
    where C.C is not null
  ) as T
pivot
  (
    min(C) for rn in ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9])
  ) as P

SE-Data

+2

More articles:


All Articles