We are all familiar with seeing our data in a vertical format from SSMS. Here is a sample:

SELECT  *
FROM    #EmployeeJobs

Employee_ID	Job_id
100	500
100	501
100	502
100	503
100	504
191	501
191	502
191	503
191	505
188	503
188	504

But what if you, or your customer want’s the data horizontally? That is easy enough with a CTE. Here is a quick sample that creates a temp table with some employees and jobs.

--  Create a sample table
create table #EmployeeJobs
(     Employee_ID   int
    , Job_id        int
)    

--  Insert some values into the table
insert into #EmployeeJobs( Employee_ID, Job_id ) values
(100,500)
,(100,501)
,(100,502)
,(100,503)
,(100,504)
,(191,501)
,(191,502)
,(191,503)
,(191,505)
,(188,503)
,(188,504)

--  Use a CTE to build a list the employees and their jobs
;with CTE as
(SELECT   Employee_ID
        , Job_id
        , row_number() over (partition by Employee_ID order by Job_id) as Numbers
FROM    #EmployeeJobs
)

--  Display all of the user jobs
select    EMPLOYEE_ID
        , max(case when Numbers = 1 then Job_id else null end) as Job_1
        , max(case when Numbers = 2 then Job_id else null end) as Job_2
        , max(case when Numbers = 3 then Job_id else null end) as Job_3
        , max(case when Numbers = 4 then Job_id else null end) as Job_4
        , max(case when Numbers = 5 then Job_id else null end) as Job_5
        , max(case when Numbers = 6 then Job_id else null end) as Job_6
from    CTE
group by EMPLOYEE_ID

Now, the results will look like this:

EMPLOYEE_ID	Job_1	Job_2	Job_3	Job_4	Job_5	Job_6
100	500	501	502	503	504	NULL
188	503	504	NULL	NULL	NULL	NULL
191	501	502	503	505	NULL	NULL

This probably isn’t the best way to get results in this fashion, but it is certainly quick and easy. Thanks to the guys at SQLServerCentral.com for this one!