By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| @@version |
|---|
| 8.0.27 |
create table sampletable(fileid int,contolnames varchar(50))
create table Files(fileid int,file1 varchar(50),file2 varchar(50))
insert into sampletable values(1,'abc'),(2,'ghf'),(3,'hjy'),(4,'juk')
insert into Files values (1,'abc.pdf','abcdef.pdf'),(2,'ghf.pdf','ghfjkl.pdf'),
(3,'hjy.pdf','hjyui.pdf')
select f.fileid, st.contolnames as names, case when t.x=1 then f.file1 else file2 end as Files from Files f
inner join sampletable st on st.fileid=f.fileid
cross join
(
select 1 x union select 2 x
) t(x)
order by fileid
| fileid | names | Files |
|---|---|---|
| 1 | abc | abc.pdf |
| 1 | abc | abcdef.pdf |
| 2 | ghf | ghf.pdf |
| 2 | ghf | ghfjkl.pdf |
| 3 | hjy | hjy.pdf |
| 3 | hjy | hjyui.pdf |