SQL Server 2022

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

CREATE TABLE mytable (
  BedType varchar(20),	
  RoomId int,
  FirstName varchar(20)
);

insert into mytable values
('Double',	4,	'Andrew'),
('Double',	4,	'Janet'),
('Double',	5,	'Anne'),
('Double',	5,	'Robert'),
('Double',	7,	'Michael'),
('Double',	7,	'Steven'),
('Triple',	8,	'Howard'),
('Triple',	8,	'John'),
('Triple',	8,	'Carlos'),
('Triple',	9,	'Jaime'),
('Triple',	9,	'Giovanni'),
('Triple',	9,	'Rene'),
('Double',	10,	'Alejandra'),
('Double',	10,	'Pascale'),
('Single',	11,	'Paolo');
15 rows affected

select case when rn = 1 then BedType else null end as BedType, RoomId, FirstName
from (
  select *, row_number() over (partition by RoomId order by FirstName) as rn
  from mytable
) s


BedType
RoomId
FirstName


Double
4
Andrew

null
4
Janet

Double
5
Anne

null
5
Robert

Double
7
Michael

null
7
Steven

Triple
8
Carlos

null
8
Howard

null
8
John

Triple
9
Giovanni

null
9
Jaime

null
9
Rene

Double
10
Alejandra

null
10
Pascale

Single
11
Paolo


select case when lag_BedType is null then BedType else null end as BedType, RoomId, FirstName
from (
  select *, lag(BedType) over (partition by RoomId order by FirstName) as lag_BedType
  from mytable
) s


BedType
RoomId
FirstName


Double
4
Andrew

null
4
Janet

Double
5
Anne

null
5
Robert

Double
7
Michael

null
7
Steven

Triple
8
Carlos

null
8
Howard

null
8
John

Triple
9
Giovanni

null
9
Jaime

null
9
Rene

Double
10
Alejandra

null
10
Pascale

Single
11
Paolo

BedType	RoomId	FirstName
Double	4	Andrew
null	4	Janet
Double	5	Anne
null	5	Robert
Double	7	Michael
null	7	Steven
Triple	8	Carlos
null	8	Howard
null	8	John
Triple	9	Giovanni
null	9	Jaime
null	9	Rene
Double	10	Alejandra
null	10	Pascale
Single	11	Paolo