CODEWITHSUNDEEP
sqlsql-servert-sql
BillyChan
BillyChan

Reputation: 21

Any way to combine rows together?

I want to combine rows so that my result query are same as the correct result query.

My code:

SELECT space_num,
       name AS employee,
       NULL AS employee2
FROM (dbo.employee
      INNER JOIN dbo.occupancy ON employee.id = occupancy.employee_id)
RIGHT JOIN dbo.space ON occupancy.space_id = space.id
UNION
SELECT space_num,
       NULL AS employee,
       name AS employee2
FROM (dbo.employee
      INNER JOIN dbo.allocation ON employee.id = allocation.employee_id)
RIGHT JOIN dbo.space ON allocation.space_id = space.id

My result:

my result query

Expected result:

Correct result query

Upvotes: 2

Views: 75

Answers (1)

Serg
Serg

Reputation: 22811

Most probably

SELECT e.name as employee, occupiedSpace, allocatedSpace
FROM dbo.employee e
LEFT JOIN (
    SELECT COALESCE(oNm.employee_id, aNm.employee_id) as employee_id
      , aNm.space_num as occupiedSpace 
      , oNm.space_num as allocatedSpace
    FROM
        (SELECT employee_id, rn = row_number() over (partition by employee_id order by id), s.space_num
        FROM dbo.occupancy sp
        JOIN dbo.space s
        ON sp.space_id = s.id
        ) oNm
    FULL JOIN 
        (SELECT employee_id, rn = row_number() over (partition by employee_id order by id), s.space_num
        FROM dbo.allocation sp
        JOIN dbo.space s
        ON sp.space_id = s.id
        ) aNm
    ON oNm.employee_id = aNm.employee_id and oNm.rn= aNm.rn
) t ON e.employee_id = t.employee_id

Upvotes: 1

Related Questions