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 |