How to split values ​​of one column into several columns based on other column values

Question

I am trying to split the values ​​of one column into several columns based on another column value, I could get it, but I can’t remove the extra null values ​​that I get

Table

create table tbl1 (id int, strtype varchar(50), strvalue varchar(20)); insert into tbl1 values (1, 'name', 'a'),(1, 'value', 'a1'),(1, 'name', 'b'),(1, 'value', 'b1'); 

Desired Conclusion

 NAME VALUE a a1 b b1 

sql i tried

 select (case when strtype='name' then strvalue end) as name, (case when strtype='value' then strvalue end) as value from tbl1 

Answer 1

Check out the script below and hope this help helps you:

 Select t1.strValue , t2.strvalue from tbl1 t1 inner join tbl1 t2 on t1.id = t2.id where t1.strtype = 'name' and t2.strtype = 'value' and t1.strvalue = LEFT(t2.strvalue ,1) 

Answer 2

If it is not, this should help you.

 SELECT * FROM (SELECT * FROM tbl1) a PIVOT (Max(strvalue) FOR strtype IN (name,value)) pv UNION SELECT * FROM (SELECT * FROM tbl1) a PIVOT (Min(strvalue) FOR strtype IN (name,value)) pv