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!
