A version of your data that can be worked with is as follows:
create table #t (cells varchar(15), f2 int, f3 int, f4 int);
insert #t values
('seq_num', 1, 2, 3),
('linkA', 4290, 42521, 42551),
('linkB', 0, 0, 0),
('linkC', 1332, 0, 15);
As avery_larry mentions, this seems like a basic unpivot. I think where you may be thrown off is that 'seq_num' is redundant with the column names. Columns 'f2' to 'f600' are already in order. So just unpivot, extract the integer from the unpivoted column label, and subtract one. And in doing all this, ignore the 'seq_num' row.
select up.cells,
seq_num = try_convert(int,replace(up.col, 'f', '')) - 1,
up.val
from #t
unpivot (val for col in (f2, f3, f4)) up -- you write out to 'f600'
where cells <> 'seq_num';
If you would like to avoid writing out 'f2' to 'f600', you can go dynamic. First build the string that holds all the column names, and then insert that into the 'in' statement.
declare
@cols varchar(max) = '',
@f int = 1,
@maxF int = 4; -- you change to 600
while @f < @maxF begin
set @f += 1;
set @cols += 'f' + convert(varchar(3), @f) + iif(@f <> @maxF, ',', '');
end
declare @sql nvarchar(max) = '
select up.cells,
seq_num = try_convert(int,replace(up.col, ''f'', '''')) - 1,
up.val
from #t
unpivot (val for col in (' + @cols + ')) up
where cells <> ''seq_num''
';
exec (@sql);
Edit: Accounting for 'seq_num' not being a simple offset of the field headers
Okay, so you say seq_num isn't just an offset of the column names. Imagine that we swapped out the seq_num lin in the table insert with the following:
insert #t values
('seq_num', 2, 4, 8),
...
You'll have to pretty much separate it from the rest of the rows and treat it independently. But the fortunate thing is that you act on it in parallel fashion. That is, just like my query above unpivots the data for cells <> 'seq_num', you do the exact same thing except for cells = 'seq_num'. You'll need to join the two result sets back together so output the unpivoted field label in each.
Replace the @sql variable above with this:
declare @sql nvarchar(max) = '
select up.cells,
up.col,
up.val
from #t
unpivot (val for col in (' + @cols + ')) up
where cells @operator ''seq_num''
';
Note that I output up.col, instead of modifying it, and I also replace '<>' with '@operator'. Unlike @cols, '@operator' is directly part of the surrounding string.
Right now, @sql is acting as a template. Use it to create one instance for cells being equal to 'seq_num' and one for cells not equal to 'seq_num'. These instances should be in their own CTE expressions, which you subsequently re-join in the final statement. Overwrite the existing @sql variable with this statement and then execute it, as shown below:
set @sql = '
with
linkVals as (' + replace(@sql, '@operator', '<>') + '),
seqVals as (' + replace(@sql, '@operator', '=') + ')
select l.cells,
seq_num = s.val,
l.val
from linkVals l
join seqVals s on l.col = s.col
';
print (@sql);
exec (@sql);
I put in a print statement in case the final form of @sql is not clear, though you'll likely want to reformat it.
