SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table Comment
(
  [Key] int,
  [Type] nvarchar(1),
  [Number] nvarchar(4), --> changing to numeric type will drop the leading zeros
  [Text] nvarchar(20)
)

insert into Comment ([Key], [Type], [Number], [Text]) values
(1, 'A', '0001', 'SAMPLETEXT'),
(1, 'A', '0002', 'SAMPLETEXT2'),
(1, 'B', '0001', 'SAMPLETEXT3'),
(1, 'B', '0002', 'SAMPLETEXT4'),
(1, 'B', '0003', 'SAMPLETEXT5'),
(2, 'C', '0001', 'SAMPLETEXT6'),
(2, 'C', '0002', 'SAMPLETEXT7'),
(3, 'A', '0001', 'SAMPLETEXT8');

select * from Comment;


Key
Type
Number
Text


1
A
0001
SAMPLETEXT

1
A
0002
SAMPLETEXT2

1
B
0001
SAMPLETEXT3

1
B
0002
SAMPLETEXT4

1
B
0003
SAMPLETEXT5

2
C
0001
SAMPLETEXT6

2
C
0002
SAMPLETEXT7

3
A
0001
SAMPLETEXT8


create table Result
(
  [Key] int,
  [Type1] nvarchar(1),
  [Type2] nvarchar(1),
  [Type3] nvarchar(1),
  -- repeat for [Type4] to [Type19]
  [Type20] nvarchar(1),
  [Text1] nvarchar(20),
  [Text2] nvarchar(20),
  [Text3] nvarchar(20),
  -- repeat for [Text4] to [Text19]
  [Text20] nvarchar(20)
);

select * from Result;


Key
Type1
Type2
Type3
Type20
Text1
Text2
Text3
Text20


with piv1 as
(
  select piv.[Key] as [Key],
         piv.[0001] as [Type1],
         piv.[0002] as [Type2],
         piv.[0003] as [Type3],
         -- repeat for [Type4] to [Type19]
         piv.[0020] as [Type20]
  from ( select c.[Key], c.[Type], c.[Number] from comment as c ) as comm
  pivot (min(comm.[Type]) for comm.[Number] in ([0001], [0002], [0003], [0020])) piv -- add values [0004] to [0019]
),
piv2 as
(
  select piv.[Key] as [Key],
         piv.[0001] as [Text1],
         piv.[0002] as [Text2],
         piv.[0003] as [Text3],
         -- repeat for [Text4] to [Text19]
         piv.[0020] as [Text20]
  from ( select c.[Key], c.[Text], c.[Number] from comment as c ) as comm
  pivot (min(comm.[Text]) for comm.[Number] in ([0001], [0002], [0003], [0020])) piv -- add values [0004] to [0019]
)
insert into Result ([Key],
                    [Type1], [Type2], [Type3], [Type20], -- add columns [Type4] to [Type19]
                    [Text1], [Text2], [Text3], [Text20]) -- add columns [Text4] to [Text19]
select piv1.[Key],
       piv1.[Type1], piv1.[Type2], piv1.[Type3], piv1.[Type20], -- add columns piv1.[Type4] to piv1.[Type19]
       piv2.[Text1], piv2.[Text2], piv2.[Text3], piv2.[Text20]  -- add columns piv2.[Text4] to piv2.[Text19]
from piv1
join piv2 on piv2.[Key] = piv1.[Key];
3 rows affected

select * from Result;


Key
Type1
Type2
Type3
Type20
Text1
Text2
Text3
Text20


1
A
A
B
null
SAMPLETEXT
SAMPLETEXT2
SAMPLETEXT5
null

2
C
C
null
null
SAMPLETEXT6
SAMPLETEXT7
null
null

3
A
null
null
null
SAMPLETEXT8
null
null
null

Key	Type	Number	Text
1	A	0001	SAMPLETEXT
1	A	0002	SAMPLETEXT2
1	B	0001	SAMPLETEXT3
1	B	0002	SAMPLETEXT4
1	B	0003	SAMPLETEXT5
2	C	0001	SAMPLETEXT6
2	C	0002	SAMPLETEXT7
3	A	0001	SAMPLETEXT8

Key	Type1	Type2	Type3	Type20	Text1	Text2	Text3	Text20
1	A	A	B	null	SAMPLETEXT	SAMPLETEXT2	SAMPLETEXT5	null
2	C	C	null	null	SAMPLETEXT6	SAMPLETEXT7	null	null
3	A	null	null	null	SAMPLETEXT8	null	null	null