tsql - multiple rows in one row -

so have table, looks this:

id  |  parentid  |   name   |  typeid --------------------------------------- 1   |      2     |  thing1  |    1 2   |      4     |  region1 |    0 3   |      4     |  region2 |    0 4   |     null   |  region3 |    0 5   |      3     |  foo1    |    2 5   |      3     |  foo2    |    2 6   |      3     |  bar1    |    3 

what need, output this:

id  |  region  |   thing  |  foo  |  bar -------------------------------------------- 1   |  region1 |  thing1  |  null |  null 2   |  region2 |  null    |  foo1 |  bar1 3   |  region2 |  null    |  foo2 |  null 4   |  region3 |  null    |  null |  null 

with this

select id,     (case when [typeid] = 0 [name] end) region,     (case when [typeid] = 1 [name] end) thing,     (case when [typeid] = 3 [name] end) foo,     (case when [typeid] = 5 [name] end) bar [my].[dbo].[foobarthingtable] type = 0 or type = 1 or type = 3 or type = 5  

i (logically):

id  |  region  |   thing  |  foo  |  bar -------------------------------------------- 1   |  null    |  thing1  |  null |  null 2   |  region1 |  null    |  null |  null 3   |  region2 |  null    |  null |  null 4   |  region3 |  null    |  null |  null 5   |  null    |  null    |  foo1 |  null 6   |  null    |  null    |  foo2 |  null 7   |  null    |  null    |  null |  bar1 

how "foos", "bars" , "things" in same row (parent) regions? jpa way (criteriaquery) nice, since have display overview on jsf page.

thx

finally, i've got it

;with numbersequence( number ) (     select 1 number         union     select number + 1         numbersequence         number < 100 ), base_list (     select        isnull(t2.name, t1.name) region,        t1.name,        t1.typeid     foobarthingtable t1     left join foobarthingtable t2 on t1.[parentid]=t2.id , t2.typeid = 0 ) select     row_number() on (order reg_list.region, reg_list.number) id,     reg_list.region region,     thing_list.name thing,     foo_list.name foo,     bar_list.name bar (     select        t1.region,        numbersequence.number     (        select distinct            isnull(t2.name, t1.name) region        foobarthingtable t1        left join foobarthingtable t2 on t1.[parentid]=t2.id , t2.typeid = 0        t1.typeid in (0,1,2,3,5)     ) t1     cross join numbersequence ) reg_list  left join (     select        bl.region, bl.name, row_number() over(order bl.region, bl.name) rn     base_list bl     typeid =1 ) thing_list on thing_list.region=reg_list.region , reg_list.number=thing_list.rn  left join (     select        bl.region, bl.name, row_number() over(order bl.region, bl.name) rn     base_list bl     typeid =2 ) foo_list on foo_list.region=reg_list.region , reg_list.number=foo_list.rn  left join (     select        bl.region, bl.name, row_number() over(order bl.region, bl.name) rn     base_list bl     typeid =3 ) bar_list on bar_list.region=reg_list.region , reg_list.number=bar_list.rn  thing_list.name not null or foo_list.name not null or bar_list.name not null or  reg_list.number=1  order     reg_list.region,    reg_list.number