SQL rearrange row

user_13549459 0
Viewed 41

I have query and return value 

select * from table1

Result:

Dept    classin classincount    classout    classoutcount
----------------------------------------------------------
DeptA   ClassA   2               NULL        NULL
DeptA   ClassB   4               NULL        NULL
DeptA   NULL    NULL             ClassC       1
DeptA   NULL    NULL             ClassD       6
DeptA   ClassF  5                NULL        NULL

I want the return to look like this:

Dept    classin classincount    classout    classoutcount
----------------------------------------------------------
DeptA   ClassA     2             ClassC         1
DeptA   ClassB     4             ClassD         6
DeptA   ClassF     5             NULL          NULL

Need your help. Thank you.

sql-server sql
1 Answers

If I interpret this as wanting to align the columns based on the count columns -- smallest to largest -- you can do so using aggregation or a join. 

select coalesce(ci.dept, co.dept) as dept,
       ci.classin, ci.classincount,
       co.classout, co.classoutcount
from (select t.*,
             row_number() over (partition by dept order by classincount asc) as seqnum
      from t
      where classincount is not null
     ) ci full join
     (select t.*,
             row_number() over (partition by dept order by classoutcount asc) as seqnum
      from t
      where clssoutcount is not null
     ) co
     on ci.dept = co.dept and ci.seqnum = co.seqnum
user_11440350
Related