By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t1(Room int, BLOK varchar(6), DATA int)
insert into t1(Room, BLOK,DATA)
select * from
(
select 1 as Room, 'A' as BLOK, 12 as DATA
union all
select 2,'A',13
union all
select 1,'B',14
union all
select 3,'B',15
)A
4 rows affected
declare @sql varchar(max)='',@col_list varchar(8000)=''
set @col_list = (select distinct quotename([BLOK])+',' from t1
for xml path(''))
set @col_list = left (@col_list,len(@col_list)-1)
set @sql = 'select Room,'+@col_list+' from
t1
pivot (max([DATA]) for [BLOK] in ('+@col_list+'))pv'
exec(@sql)
| Room | A | B |
|---|---|---|
| 1 | 12 | 14 |
| 2 | 13 | null |
| 3 | null | 15 |