create table #a (i int, sub1 char(1), sub2 char(1), sub3 char(1), sub4 char(1))
insert #a values(1,'z','a','y','g')
insert #a values(2,'q','y','d','e')
insert #a values(3,'m','v','p','w')
insert #a values(4,'o','x','k','a')

select * from #a  -- regular results

;with u(i, cat, z, sub) AS (
select i, cat, z, 'sub' + cast(row_number() over (partition by i order by i, z) as varchar) sub
from (
	select i, sub1, sub2, sub3, sub4 from #a) ua 
	unpivot(z for cat in (sub1, sub2, sub3, sub4)) ub)
select i, [sub1], [sub2], [sub3], [sub4] 
from (select i, z, sub from u) pa
pivot (max(z) for sub in ([sub1], [sub2], [sub3], [sub4]) ) pb  -- results with sorted subs

drop table #a