我正试图根据另一列值将单列值拆分为多列,我可以得到它,但我无法删除我得到的的额外空值
表
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');
所需输出
NAME VALUE
a a1
b b1
sql我尝试过
select
(case when strtype='name' then strvalue end) as name,
(case when strtype='value' then strvalue end) as value
from tbl1
检查下面的脚本,希望它能帮助你:
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)
如果我没有错,这应该会对你有所帮助。
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