RickCJ7 RickCJ7 - 2 months ago 7
SQL Question

Putting SQL record values in a single row

SQL isn't my expertise but, how do I write a SQL query that takes values from two different records and put them in one row? For example, I did a query on this

EmployeeId
, and I need the output values specifically from the
Vacation_Type
column for Adjust on
2016-07-01
(if it exists) and the Forward value from date
2016-08-01
(exists for every employee) ? Desired output would be:

26, SL, 547.58, -37.42


Query Output

Not every employee would have an Adjust record, they have an Adjust when they are over the sick leave cap... and not everyone is over the cap. Thanks!

Answer
select
    EV.EmployeeID,
    EV.Vacation_Kind,
    stuff( (    select ', ' + convert(varchar(30), EV1.Value) from EmployeeVacations EV1
                where EV1.EmployeeID = EV.EmployeeID and EV1.Vacation_Kind = EV.Vacation_Kind
                and EV1.VacationType in ('Adjust','Forward') and EV1.CreationDate IN (EV.CreationDate, dateadd(month, datediff(month, 0, dateadd(month, 1, EV.CreationDate)), 0))
                 for xml path('')
            ), 1, 1, '')
from
    EmployeeVacations EV
where
    EV.EmployeeID = 26 and EV.Vacation_Kind = 'SL'and VacationType = 'Adjust'
group by
    EV.EmployeeID, EV.Vacation_Kind, EV.CreationDate
Comments