Matching tables columns to get data from table

If I had the following tables:
Field Definition table: defines the field captions

FIELD_DEF
FIELDID | FIELD CAPTION 
F1      | CAPTION 1
F2      | CAPTION 2

Data table: holds the actual data for each field

DATA_TABLE
DATAID  | F1 |  F2
1       | A  |  B

This is what the output needs to look like.

DATAID  | CAPTIONTITLE  | DATA
1       | CAPTION 1     | A
1       | CAPTION 2     | B

The data column names correspond to the field captions in the FIELD_DEF. I need the field caption and corresponding data in the same row.

I don’t know how to achieve this. If possible, I would like to use a single SQL query.

>Solution :

Assuming you have many columns.

Here is an option that will dynamically UNPIVOT your data without actually using Dynamic SQL.

Example

Select A.DataID
      ,CAPTIONTITLE  = C.[FIELD CAPTION]
      ,Data          = B.Value
 from  Data_Table A
  Cross Apply ( Select * 
                From  OpenJson((Select A.* For JSON Path,Without_Array_Wrapper )) 
                Where [Key] Not in ('DataID','Other','Colums2Exclude')
             ) B
 Join Field_Def C on B.[key]=C.FIELDID collate SQL_Latin1_General_CP1_CI_AS

Results

DataID  CAPTIONTITLE    Data
1       CAPTION 1       A
1       CAPTION 2       B