sql - how to select all not NULL values in from every column... Need LINQ or ADO query? -

please answer question... m trying make single table instead of having multiple tables

table structure like

id    col1       col2      col3   ...  ... 1     shirt      null      null 2     skirt      null      null 3     trouser    null      null 4     null       winter    null 5     null       summer    null 6     null       autumn    null 7     null       null      night 8     null       null      evening 9     null       null      null .     ..         ..        .. 

we can achieve using row_number ,order , common table expression

declare @table table(id int,col1 varchar(10),col2 varchar(10),col3 varchar(10)) insert @table(id,col1,col2,col3)values (1,'shirt',null,null) , (2,'skirt',null,null),(3,'trouser',null,null),(4,null,'winter',null),(5,null,'summer',null) ,(6,null,'autumn ',null),(7,null,null,'night'),(8,null,null,'evening') ;with cte ( select col1,row_number()over( order (select null))r @table col1 not null ), cte1 ( select col2,row_number()over( order (select null))r @table col2 not null ), cte2 ( select col3,row_number()over( order (select null))r @table col3 not null ) select c.col1,cc.col2,ccc.col3 cte c inner join cte1  cc on c.r = cc.r left join cte2 ccc on  ccc.r = c.r