Select column name dynamically based on rows from another table in SQL server I want to select column name from another table row value in single query.

CREATE TABLE [dbo].[DynamicFieldsMaster](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](max) NULL,
    [DisplayName] [nvarchar](max) NULL,
    [Type] [nvarchar](max) NULL,
 CONSTRAINT [PK_DynamicFieldsMaster] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO



Id    Name    DisplayName Type
1 Field1  ID  int
2 Field2  NAME    string
3 Field3  EMAIL   string
4 Field4  ADDRESS textarea



And my Another table is ...

CREATE TABLE [dbo].[DynamicFieldsValue](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [CustomerID] [int] NULL,
    [Field1] [int] NULL,
    [Field2] [nvarchar](max) NULL,
    [Field3] [nvarchar](max) NULL,
    [Field4] [nvarchar](max) NULL,
    [Field5] [bit] NULL,
 CONSTRAINT [PK_DynamicFieldsValue] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO



Id    CustomerID  Field1  Field2      Field3     Field4      Field5
1       1         101     testField2  testField3 testField4   1
2       2         1       tert        erter      terter       1
3       3         7       tertretert  ertert     erterertert  1
4       4         12      ertert      erter      tertr        1
5       5         11      add         jhgjh      jhghjghjghj  1



and i want result like

SELECT *, (SELECT v.Field1(dynamic column name)  FROM DynamicFieldsValue v WHERE CUSTOMERID =1 ) VALUE FROM [dbo].[DynamicFieldsMaster]  DM


For customerid =1 i need to retrive all data from dynamicfieldvalue table

Id  Name    DisplayName Type    VALUE
1   Field1  ID  int             101  
2   Field2  NAME    string      testField2
3   Field3  EMAIL   string      testField3
4   Field4  ADDRESS textarea    testField4
5   Field5  GENDER  boolean     1


any one please help me
It's probably not the optimal way to do it and I would probably look at normalizing the database but here's a way to get the data you want.

with cte as (  
select 'field1' "name", convert(nvarchar(max),field1) "value" from [dbo].[DynamicFieldsValue] where customerID=1  
union  
select 'field2' "name", field2 "value" from [dbo].[DynamicFieldsValue] where customerID=1 
union  
select 'field3' "name", field3 "value" from [dbo].[DynamicFieldsValue] where customerID=1  
)  
select * from [DynamicFieldsMaster] dm  
join cte on dm.name = cte.name