create table tab_news
 (nId int ,sId_FK int,nTitle varchar(100),pic1 varchar(100),pic2 varchar(100),pic3 varchar(100),pic4 varchar(100))

insert into tab_news
select 1, 1, 'title1', 'a1.jpg', 'b1.jpg', 'c1.jpg', 'd1.gif' union all
select 2, 1, 'title2', 'a2.jpg', 'b2.jpg', 'c2.jpg', 'd2.gif'
 

create table tab_Serial
 (sId int ,sTitle varchar(100),picHome varchar(100),picA1 varchar(100),picA2 varchar(100),picA3 varchar(100),picA4 varchar(100))

insert into tab_Serial
select 1,  'sTitle1 ', '', 'a3.jpg', 'b3.jpg', 'c3.jpg', 'd3.gif'


;with cte as 
(
    select vId=ROW_NUMBER ()over(partition by b.sId_FK order by b.imgName),* from (
            select * from
            (
            select sId_FK ,  imgName = pic1 from tab_news 
            union all
            select sId_FK ,  imgName = pic2 from tab_news
            union all
            select sId_FK ,  imgName = pic3 from tab_news
            union all
            select sId_FK ,  imgName = pic4 from tab_news
            union all
            select sId AS sId_FK,  imgName = picA1 from tab_Serial 
            union all
            select sId AS sId_FK,  imgName = picA2 from tab_Serial
            union all
            select sId AS sId_FK,  imgName = picA3 from tab_Serial
            union all
            select sId AS sId_FK,  imgName = picA4 from tab_Serial
            ) a
    ) b
)

select * from cte

--结果
vId                  sId_FK      imgName
-------------------- ----------- --------
1                    1           a1.jpg
2                    1           a2.jpg
3                    1           a3.jpg
4                    1           b1.jpg
5                    1           b2.jpg
6                    1           b3.jpg
7                    1           c1.jpg
8                    1           c2.jpg
9                    1           c3.jpg
10                   1           d1.gif
11                   1           d2.gif
12                   1           d3.gif