----------------
id,col1,col2
----------------
1,a,NULL
1,NULL,b
----------------
↓
----------------
id,col1,col2
----------------
1,a,ｂ
----------------

にするには
----------------------------

select
a.id,
a.col1,
b.col2
from
ta2 a
inner join
ta2 b
on a.id = b.id
where
a.col1 is not null
and
b.col2 is not null
----------------------------

また

----------------
id,col1,col2
----------------
1,a,NULL
1,NULL,b
2,c,null
3,null,d
----------------
を
----------------
id,col1,col2
----------------
1,a,ｂ
2,c,null
3,null,d
----------------

とするには

---両方カラムがあるフィールドを抽出
select
a.id,
a.col1,
b.col2
from
ta2 a
inner join
ta2 b
on a.id = b.id
where
a.col1 is not null
and
b.col2 is not null

union all
--col1だけあるフィールドを抽出
select
a.id,
a.col1,
b.col2
from
ta2 a
inner join
ta2 b
on a.id = b.id
where
a.col1 is not null
and
a.id not in
(
select
a.id
from
ta2 a
inner join
ta2 b
on a.id = b.id
where
a.col1 is not null
and
b.col2 is not null
)
union all
--col2だけあるフィールドを抽出
select
a.id,
a.col1,
b.col2
from
ta2 a
inner join
ta2 b
on a.id = b.id
where
b.col2 is not null
and
a.id not in
(
select
a.id
from
ta2 a
inner join
ta2 b
on a.id = b.id
where
a.col1 is not null
and
b.col2 is not null
)



あとは、以下のページも参考に


複数行のグループデータを1行にまとめるSQL
https://ginpro.winofsql.jp/article/253189068.html


縦横の変換が、数値であれば、SUMを使うともっとシンプルにできる
https://johobase.com/sqlserver-expand-data-horizontally/

MAXを使えば、文字もOK
https://qiita.com/k24d/items/79bc4828c918dfeeac34
SELECT uid,
max(CASE WHEN key = 'c1' THEN value END) AS c1,
max(CASE WHEN key = 'c2' THEN value END) AS c2,
max(CASE WHEN key = 'c3' THEN value END) AS c3
FROM vtable
GROUP BY uid