sql - Dynamic Order By using parent value and mixed column types -

i have table named report, columns in report followed:

id(char)      name(char)      parentid(char)      sequence(int)      sortby(char) 

the table has hierarchies. each row's parentid row's id(the top hierarchy's parentid null). sortby filed either "name" or "sequence".

now want select * report. result want group parentid, in each group, ordered sortby, sortby's value in row id = group 's parentid.

more specifically, if group's parentid "animal", sortby of row id "animal" "name", want group sorted "name".

is can help? time!

you can use case in order dynamically sort parent. tricky issue case columns can sort different data types. in case have cast them common data type.

query

the query simple. left join parent , use parent determine second sort parameter in case. first sort parameter parentid -- keeps children grouped together.

there 2 ways handle sequence. can either cast varachar , use pad, or add third sort parameter. there no change in execution plan, expect separating order columns type better if there index sequence column

select child.* #report [child] left join #report [parent] on parent.id = child.parentid order      child.parentid,     case parent.sortby when 'name' child.name end,     sequence -- sort sequence if no column matched 

alternative order by

order      child.parentid,     case parent.sortby         when 'name' child.name         else right('0000000000' + cast(child.sequence varchar), 10)     end 

setup code

if exists (select * tempdb.sys.objects name '#report%')      drop table #report;  create table #report (     id char(20),          name char(20),     parentid char(20),     sequence int,     sortby char(20) );  insert #report values ('a', 'zname', 'f', 2, ''), ('b', 'bname', 'f', 3, ''), ('c', 'cname', 'g', 7, ''), ('d', 'dname', 'g', 5, ''), ('e', 'ename', 'g', 6, ''), ('f', 'fname', '', 9, 'name'), ('g', 'gname', '', 8, 'sequence'); 

output

id  name    parentid    sequence    sortby f   fname               9           name                 g   gname               8           sequence             b   bname    f          3                          zname    f          2                        d   dname    g          5                        e   ename    g          6                        c   cname    g          17