sql - how can i remove the null values in two columns to show only data -

i have requirement have converted row values columns i'm trying remove null rows , show data

declare @t table (id int,keys varchar(10),val varchar(10)) insert @t (id,keys,val)values (1,'name','hulk'),(2,'age','22'),(3,'name','ironman'),(4,'age','35') ;with cte ( select  [name],cast([age] int)age ( select id,keys,val @t )t pivot(max(val) keys in ([name],[age]))p  group [name],age) select  (c.name),(c.age) cte c  left join cte cc on c.age = cc.age , c.name = cc.name 

result :

name    age null    22 null    35 hulk    null ironman null 

desired result set :

name    age hulk    22 ironman 35 

i agree gordon, data structure flawed. if however, can assume age key id follows name key id 1, following work. if can change table structure, better..

declare @t table (id int,keys varchar(10),val varchar(10)) insert @t (id,keys,val) values (1,'name','hulk'),(2,'age','22'),(3,'name','ironman'),(4,'age','35')   select nc.id,nc.val name,xx.age @t nc join (select id,val age @t keys='age') xx on xx.id=nc.id+1 keys='name' 

however, code relies on possibly bad assumption keys...