Get not null column from different columns for a row

1

Hello,

I have a table like below. I want to get just id and not null column for that. can you tell please how to do that?

Id column1 column2 column3 column4 column5 ......
1 0 0 0 4 0
2 3 0 0 0 0
3 0 6 0 0 0
4 0 0 5 0 0

I want to have this as finally

Id new column
1 4
2 3
3 6
4 5

2

0 is not null value;
how many columns do you have? do you have a fix number of columns ?

here is a example, working with a fix number of columns:

declare @Source as table
(id int not null
,col1 int not null
,col2 int not null
,col3 int not null
,col4 int not null)

insert into @source(id,col1,col2,col3,col4)
values(1,0,0,3,0);

select id, coalesce(NULLIF(col1,0),NULLIF(col2,0),NULLIF(col3,0),NULLIF(col4,0))
from @source