This is a strange requirement and I don't know of a way to make this simple. You could make this dynamic based on the number of rows in the table but the row count will get overwhelming very quickly.
Here is one way you could tackle this. It isn't very efficient but the requirements here are sufficiently against the norm for relational data. If this is a persistent list I would store this in another table and regenerate it on an as need basis instead of running this kind of thing over and over.
This at least returns the results you stated you want although the names are not in the same order as I didn't really understand the logic of how those names were ordered within each row.
declare @Something table (CategoryID int, Name varchar(10))
insert @Something values
(1, 'Brody')
, (2, 'Chad')
, (3, 'Stacy')
, (4, 'Jessica')
select Name1 = s.Name
, Name2 = null
, Name3 = null
, Name4 = null
from @Something s
UNION ALL
select s.Name
, s2.Name
, null
, null
from @Something s
cross join @Something s2
where s.Name < s2.Name
UNION ALL
select s.Name
, s2.Name
, s3.Name
, null
from @Something s
cross join @Something s2
cross join @Something s3
where s.Name < s2.Name
and s.Name < s3.Name
and s2.Name < s3.Name
UNION ALL
select s.Name
, s2.Name
, s3.Name
, s4.Name
from @Something s
cross join @Something s2
cross join @Something s3
cross join @Something s4
where s.Name < s2.Name
and s.Name < s3.Name
and s2.Name < s3.Name
and s.Name < s4.Name
and s2.Name < s4.Name
and s3.Name < s4.Name
