sql - SQL Server pivot a table without aggregating

Question

Is it possible to pivot a table without aggregating or roll up the nulls?

For example I have a query something like

select 
    PERS_AREA_NAME,
    EMP_FIRST_NAME + ' ' + EMP_LAST_NAME as 'CHIEF COUNSEL',
    NULL AS 'APPROVER'
from 
    dbo.IES_HR_EMPLOYEES
where 
    JOB_NAME like '%Chf Cnsl%'  

UNION ALL  

select 
    PERS_AREA_NAME,
    NULL,
    EMP_FIRST_NAME + ' ' + EMP_LAST_NAME
from 
    dbo.IES_HR_EMPLOYEES
where
    JOB_NAME like '%Dep Sec%'
order by 
    1

That results in

PERS_AREA_NAME CHIEF_COUNSEL APPROVER
-----------------------------------------
one            Bob Jones     Null
one            Fred Smith    Null
one            Null          Mary Smith
two            John Doe      Null
two            Null          Will Jones
two            Null          Fred Stevens

Is it possible to rollup the nulls?

    PERS_AREA_NAME  CHIEF_COUNSEL APPROVER
    ------------------------------------------------
    one             Bob Jones     Mary Smith
    one             Fred Smith    Null
    two             John Doe      Will Jones
    two             Null          Fred Stevens

标签： sqlsql-server

Answer 1

Instead of doing a UNION ALL of the two queries, do a FULL OUTER JOIN using ROW_NUMBER() as the JOIN condition.