I might be misinterpreting your requirements but it sounds like you can use the UNPIVOT/PIVOT function to get the results you need.
If you know the number of columns to transform, then you can hard-code a static version of this.
select *
from
(
select AccNo,
val,
col + cast(rn as varchar(10)) Col_Name
from
(
select p1.accno,
p1.name,
p1.itemid,
i1.id,
i1.i_name,
row_number() over(partition by accno order by name) rn
from persons p1
left join items i1
on p1.itemid = i1.id
) x
unpivot
(
val
for col in (name, i_name)
) u
) x1
pivot
(
max(val)
for col_name in ([name1], [i_name1], [name2], [i_name2])
) p
But it sounds like you will have an unknown number of items to transform to columns, so you can use dynamic SQL and create a dynamic version of this:
DECLARE @colsUnPivot AS NVARCHAR(MAX),
@colsPivot AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
SET @colsUnPivot = stuff((select ','+C.name
from sys.columns as C
where (C.object_id = object_id('persons')
or C.object_id = object_id('items'))
and C.name like '%name%'
for xml path('')), 1, 1, '')
select @colsPivot = STUFF((SELECT ','
+ quotename(c.name
+ cast(p.rn as varchar(10)))
from
(
select row_number() over(partition by accno order by name) rn
from persons
) p
cross apply
sys.columns as C
where (C.object_id = object_id('persons')
or C.object_id = object_id('items'))
and C.name like '%name%'
group by c.name, p.rn
order by p.rn, c.name desc
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'select *
from
(
select AccNo,
val,
col + cast(rn as varchar(10)) Col_Name
from
(
select p1.accno,
p1.name,
p1.itemid,
i1.id,
i1.i_name,
row_number() over(partition by accno order by name) rn
from persons p1
left join items i1
on p1.itemid = i1.id
) x
unpivot
(
val
for col in ('+ @colsunpivot +')
) u
) x1
pivot
(
max(val)
for col_name in ('+ @colsPivot + ')
)p'
exec(@query)