0

I have a project that has dynamic field details for an asp page. When the page is executed, the database is queried for the list of dynamic fields and then all the controls are created at runtime on the asp page. Now all these dynamic fields have corresponding data in another (or say more than one) tables.

The data in the Dynamic Control Detail table is like this:

FieldID | FieldName         |   MappingTableName |  MappingColumnName
-------------------------------------------------------------------------
92      | txtPrsnFirstName  |   Table1           |  FirstName
93      | txtPrsnLastName   |   Table1           |  LastName
94      | ddlPrsnGender     |   Table2           |  Gender
95      | txtCompany        |   Table2           |  Company
96      | txtDesignation    |   Table1           |  Designation

The corresponding datatable's data is as follows:

Table1:

PersonID |  FirstName   |  LastName   |  Designation
-----------------------------------------
1        |  Person1     |  SomeName   |  Manager
2        |  Person2     |  MoreName   |  Executive

Table2:

PersonID |  Gender      |  Company    
--------------------------------------
1        |  Male        |  ABC Cons   
2        |  Female      |  XYZ PVT.LTD

Now, currently what I have done is, created a stored proc, that returns all three tables data at once and I would fetch each one of them in a DataSet then I would iterate through the FieldTable data first and then fetch the corresponding data from Table1 and Table2

I need to write a query that fetches the data in a way that the data from Table1 and Table2 will get transposed in form of multiple rows (Fetches only one record at a time) something like this.

FieldID | FieldName         |   MappingTableName |  MappingColumnName    | Data
---------------------------------------------------------------------------------------
92      | txtPrsnFirstName  |   Table1           |  FirstName            | Person1
93      | txtPrsnLastName   |   Table1           |  LastName             | SomeName
94      | ddlPrsnGender     |   Table2           |  Gender               | Male
95      | txtCompany        |   Table2           |  Company              | ABC Cons
96      | txtDesignation    |   Table1           |  Designation          | Manager
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Cyberpks
  • 1,401
  • 6
  • 21
  • 51
  • How do you find out which value belongs to which fieldname in corresponding table? how did you know data for FirstName is Person1 and not Person2 – DhruvJoshi Jan 31 '14 at 09:09
  • See, I've mentioned that there will be a single selection (you can use logic of Join). Means only one record from Table1 and Table2 will be fetched at once. The former question (of yours) has answer in the question(mine) itself; the field table has the `MappingTableName` and `MappingColumnName` which can be used to select the corresponding data from the data table. – Cyberpks Jan 31 '14 at 09:40
  • But how do you know which record in that mappingtable and mappingcolumn, you know, which row? – DhruvJoshi Jan 31 '14 at 10:50

1 Answers1

0

I would add a hidden column (I will call it PersonMap) in the Field-Table which indicates a mapping between FieldID and PersonID (meaning: Field with ID XY shows data from Person Z). Now create a function like (my TSQL is a little rusty - therefore pseudo-SQL):

create function returnPersonData(in tablename varchar, in columnname varchar, in personID varchar) RETURNS VARCHAR
BEGIN
declare res varchar;
set res = EXEC('SELECT %columnname% FROM %tablename% WHERE PersonID = %personID%');
return res;
END

Now use the created function for your Data_column. The FROM statement is your FieldTable + the PersonMap-column:

SELECT FieldID, FieldName, MappingTableName, MappingColumnName, (returnPersonData(MappingTableName, MappingColumnName, PersonMap) AS Data)
FROM (SELECT FieldID, FieldName, MappingTableName, MappingColumnName, (SELECT PersonID from somewhere) AS PersonMap FROM FieldTable ... )

I hope I covered you question... greetings.

chile
  • 724
  • 1
  • 7
  • 15