You cannot have such a dynamic column number, but you can combine your data into a row:
select s.ItemID, s.Code, s.Name, stuff( ( select ', ' + CAST(sv.SearchValueID AS VARCHAR) from ItemSearch as sv where sv.ItemID = s.ItemID for xml path(''), type ).value('.', 'nvarchar(128)') , 1, 2, '') as SearchValues from Item as s;
or you can fold the lines with the PIVOT command or manually (I prefer the latter approach, it just seems more flexible for me, but pivot can significantly reduce the amount of code in certain situations):
with cte as ( select *, row_number() over(partition by sv.ItemID order by sv.SearchValueID) as row_num from ItemSearch as sv ) select s.ItemID, s.Code, s.Name, max(case when sv.row_num = 1 then sv.SearchValueID end) as SearchValueID1, max(case when sv.row_num = 2 then sv.SearchValueID end) as SearchValueID2, max(case when sv.row_num = 3 then sv.SearchValueID end) as SearchValueID3, max(case when sv.row_num = 4 then sv.SearchValueID end) as SearchValueID4 from Item as s inner join cte as sv on sv.ItemID = s.ItemID group by s.ItemID, s.Code, s.Name
You can also include the previous statement in dynamic SQL as follows:
declare @stmt nvarchar(max) select @stmt = isnull(@stmt + ',','') + 'max(case when sv.row_num = ' + cast(rn as nvarchar(max)) + ' then sv.SearchValueID end) as SearchValueID' + cast(rn as nvarchar(max)) from ( select distinct row_number() over(partition by ItemID order by SearchValueID) as rn from ItemSearch ) as a select @stmt = ' with cte as ( select *, row_number() over(partition by sv.ItemID order by sv.SearchValueID) as row_num from ItemSearch as sv ) select s.ItemID, s.Code, s.Name,' + @stmt + ' from Item as s inner join cte as sv on sv.ItemID = s.ItemID group by s.ItemID, s.Code, s.Name;' exec dbo.sp_executesql @stmt = @stmt
demo sql