How to get column records into row in my case using SQL Server? -

i have table called relationdata has 4 columns:

relationdata

parent    child1    child2    child3 ------------------------------------ 111       112       113       117 111       222       223       224 444       441       442       443  

i want show in 1 row if each 1 of id matching.

if user search 111 or 112 or 113 or 117 or 222 or 223 or 224, has show

111 112 113  117 222 223 224    

if user searches 442, result should

444 441 442 443  

even if use case, show in column, not row

you can unpivot results. here 1 way:

select v.child relationdata rd outer apply      (values (rd.child1), (rd.child2), (rd.child3), (rd.child4)) v(child) 111 in (rd.child1, rd.child2, rd.child3, rd.child4); 

note: have 4 columns references sign of poor database design. better have relation table single child column , "child number".