SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table table1( ID int, Name varchar(20), Position varchar(20))
Insert into table1 values(1, 'Mike', 'Developer')
1 rows affected

Insert into table1 values(2, 'Mark', 'QA')
1 rows affected

select ID, Name as "User Role/Name" from table1
union all
select ID, position from table1
order by ID


ID
User Role/Name


1
Mike

1
Developer

2
QA

2
Mark