col data as column header with conditions

Hi all,

Could any one help me for below requirement

I have two table like this

src_table

 Trm_id   agt_id    nk_id  cid  valueid
 t1       a1        n1      10     101
 t2       a2        n2      10     101
 t3       a3        n3      10     102
 t1       a1        n1      11     104
 t2       a2        n2      11     105
 t3       a3        n3      11     105
 t1       a1        n1      12     106
 t2       a2        n2      12     106
 t3       a3        n3      12     107

reference_table

 ref_id   value_id   value_desc   cid
   1       101        x12         10
   2       102        x13         10
   3       103        x23         10
   4       104        y12         11  
   5       105        y13         11    
   6       106        z01         12   
   7       107        z02         12

Now I would show my result set like

Trm_id   agt_id    nk_id    cid_10   cid_11    cid_12
t1        a1        n1       X12     Y12     z01
t2        a2        n2       X12     y13     z01
t3        a3        n3       x13     y13     z02

I am trying with Pivot concept but I think pivot wont work this case.So I reached out to you.

Tags: SQL Server 2008R2, Pivot table