0

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

1

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
| improve this answer | |
0
SELECT DynamicFieldsMaster.ID,DynamicFieldsValuePivot.Name,DynamicFieldsMaster.DisplayName,DynamicFieldsMaster.Type,VALUE 
FROM  
(SELECT CUSTOMERID, CONVERT(NVARCHAR(MAX), Field1) AS Field1, Field2, Field3, Field4, CONVERT(NVARCHAR(MAX), Field5) AS Field5  
FROM DynamicFieldsValue WHERE CustomerID = 1) D  
UNPIVOT 
(VALUE FOR NAME IN(Field1, Field2, Field3, Field4, Field5)) AS DynamicFieldsValuePivot, DynamicFieldsMaster  
WHERE DynamicFieldsValuePivot.NAME = DynamicFieldsMaster.Name

I retrieve it by this way.

Maybe it is a static way for fields list I want to make it dynamic.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.