Micros SQL Queries

[FreeSoldier]

Hi All,

I have an SQL queries that will list all the employees with their job(s) and pay rate.​

select 
[indent]e.obj_num as "Payroll ID",[/indent]
[indent]e.long_first_name as "First Name" ,[/indent]
[indent]e.long_last_name as "Last Name" ,[/indent]
[indent]j.name as "Job Name" ,[/indent]
[indent]COALESCE(h.override_reg_pay_rate, j.deflt_reg_pay_rate) as 'Pay Rate'[/indent]
from micros.emp_def e
[indent]join micros.job_rate_def h on e.emp_seq = h.emp_seq [/indent]
[indent]inner join micros.job_def j on h.job_seq = j.job_seq [/indent]
where 
[indent]e.long_last_name Not Like '%**%' [/indent]
[indent]and e.long_first_name Not Like '%JackT%'[/indent]
[indent]and e.termination_date is NULL[/indent]
order by e.obj_num

For employees with multiple jobs, the query will list each job on an additional row (duplicating the employee data)
I would like to have one row per employees and have all the jobs/pay rate in one or multiple columns (if possible).
I search the web and it seems i could use a nested select with the "for xlm path ('')" to list all the job/payrate in one column but i can get the syntax working.

Any SQL Guru that could assist and modify the code above so that job/pay rare are in one or multiple columns?

Much appreciated
Thanks
FreeSoldier

 

[codexPOSed]

I can't get it totally dynamic(it might be possible using LIST function) however, you can do something like this:

select  * from( select 
e.obj_num as "PayrollID",
e.long_first_name,
j.job_seq,
COALESCE(h.override_reg_pay_rate, j.deflt_reg_pay_rate) as 'PayRate'
from micros.emp_def e
join micros.job_rate_def h on e.emp_seq = h.emp_seq 
inner join micros.job_def j on h.job_seq = j.job_seq) MyPivotSourceData
Pivot 
(
max(Payrate) 
for job_seq in (5 server,11 bartender, 12 manager)
) myPivotData
order by PayrollId