How To Create Data Become Column [closed]

How do we write a SQL query for this situation? The structure of the table is;

| Room | BLOK | DATA |
|   1  |   A  |  12  |
|   2  |   A  |  13  |
|   1  |   B  |  14  |
|   3  |   B  |  15  |

Expected Output:

| Room |   A  |  B   |
|   1  |   12 |  14  |
|   2  |   13 | null |
|   3  |  null|  15  |

Answer

You can try using conditional aggregation

DEMO

select room, max(case when BLOK='A' then DATA end) as A,
max(case when BLOK='B' then DATA end) as B
from tablename
group by room

OR Try using PIVOT

select room,pv.* from 
tablename
pivot(max(DATA) for BLOK in ([A],[B])) pv

OUTPUT:

Room    A   B
1      12   14
2      13   
3           15

For Dynamic Pivot

declare @sql varchar(max)='',@col_list varchar(8000)=''

set @col_list = (select distinct quotename([BLOK])+',' from tablename
for xml path(''))

set @col_list = left (@col_list,len(@col_list)-1)

set @sql = 'select Room,'+@col_list+' from
tablename
pivot (max([DATA]) for [BLOK] in ('+@col_list+'))pv'

exec(@sql)

FIDDLE FOR DYNAMIC PIVOT