Use a UNION to add more than one PIVOT with aggregates MAX() and MIN().
DECLARE @Job TABLE (Name CHAR(1), [Job Profile] VARCHAR (10))
INSERT INTO @Job
(Name, [Job Profile])
VALUES
('a', 'ADMIN'),
('b', 'USER'),
('c', 'USER'),
('d', 'EMPLOYEE'),
('e', 'ADMIN'),
('f', 'EMPLOYEE')
SELECT [ADMIN], [USER], [EMPLOYEE]
FROM (
SELECT *FROM @Job
) J
PIVOT (
MAX(Name) FOR [Job Profile] IN ([ADMIN], [USER], [EMPLOYEE])
) Result
UNION
SELECT [ADMIN], [USER], [EMPLOYEE]
FROM (
SELECT *FROM @Job
) J
PIVOT (
MIN(Name) FOR [Job Profile] IN ([ADMIN], [USER], [EMPLOYEE])
) Result
Use a
Dynamic PIVOT, if you do not know the exact number of Job Profiles.