title: sql pivot rows to multiple columns
body: <p><strong>can we pivot rows to multiple columns i.e</strong></p>
  
  <p><a href="https://i.stack.imgur.com/ylrZB.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ylrZB.jpg" alt="enter image description here"></a></p>
  
  <pre><code>Create table #Temp_Trans    
  
  (   
  P_ID int,   
  Custom_Name varchar(30),    
  Text_Value varchar(30), 
  Number_Value int,   
  [DateTime] datetime,    
  
  )   
  
  insert into #Temp_Trans values  
  (1111,'DepartmentCode','AAA',null,null),    
  (1111,'Year','2017',null,null), 
  (1111,'StartDate',null,null,'2002-10-02'),  
  (1111,'EmpID',null,555,null),   
  (1111,'EmpTitle','TeamLeader',null,null),   
  
  (2222,'DepartmentCode','BBB',null,null),    
  (2222,'Year','2016',null,null), 
  (2222,'StartDate',null,null,'2010-10-02'),  
  (2222,'EmpID',null,null,null),  
  (2222,'EmpTitle',null,null,null),   
  
  (3333,'DepartmentCode','CCC',null,null),    
  (3333,'Year','2017',null,null), 
  (3333,'StartDate',null,null,'2017-10-02')   
  
  select * from #Temp_Trans
  </code></pre>
  
  <p><a href="http://sqlfiddle.com/#!6/d4eb9" rel="nofollow noreferrer">http://sqlfiddle.com/#!6/d4eb9</a></p>
  
  <p>or any-other way. Most records (p_id) will have fixed number of columns (Custom Name) heading - few some and some none. Many thanks</p>