I have a database with data from a web form that has a few metadata tables so form designers can add fields to different sections of the form. The metadata is saved in rows and I need to be able to create different tables for each form created. If the forms were static (i.e., no designers would create any more forms), I could create a view or function for each form and hard-code the columns per each form. Unfortunately, I can't really keep up with it (forms get created maybe 1-2 times a month, sometimes less).
I was wondering if there's a way that I could dynamically create the tables based on the Workgroup+'_'+FormName from the data below, then dynamically pivot the column names in the create statement for the tables.
The first @source table variable is an example of the metadata. The second table variable is a simple example of an incident that writes to a field from the metadata.
DECLARE @Source TABLE (SourceId INT PRIMARY KEY IDENTITY, WorkGroup VARCHAR(MAX), FormName VARCHAR(MAX), ColumnHeaderData VARCHAR(MAX), ColumnValuesData VARCHAR(MAX)) INSERT INTO @Source (WorkGroup, FormName, ColumnHeaderData, ColumnValuesData) VALUES ('TSO', 'IncidentReport', 'Header', 'TSO Details') ,('TSO', 'IncidentReport', 'Details', 'EntryDate') ,('TSO', 'IncidentReport', 'Details', 'Notes') ,('CSR', 'TrackingReport', 'Header', 'CustomerFeedback') ,('CSR', 'TrackingReport', 'Details', 'IncidentDate') ,('CSR', 'TrackingReport', 'Details', 'FeedbackType') ,('CSR', 'TrackingReport', 'InvestigationSummary', 'Investigator') ,('CSR', 'TrackingReport', 'InvestigationSummary', 'InvestigatorNotes') ,('CSR', 'AbsenceRequest', 'Details', 'Requester') ,('CSR', 'AbsenceRequest', 'Details', 'RequestState') SELECT * FROM @Source DECLARE @IncidentColumnData TABLE (IncidentId INT PRIMARY KEY IDENTITY, IncidentDate DATE, FormSourceId INT, ColumnData VARCHAR(MAX)) INSERT INTO @IncidentColumnData (IncidentDate, FormSourceId, ColumnData) VALUES ('2018-01-01', 5, 'Just populating some info to show the correlation') SELECT * FROM @Source S LEFT JOIN @IncidentColumnData I ON I.FormSourceId=S.SourceId
Ultimately, I need to see this pivoted into new tables dynamically. I grasp dynamic sql for columns, but I'm not sure how to dynamically create the tables based on the data set.
So for example, based on the data, 3 tables would be created dynamically:
CREATE TABLE TSO_IncidentReport (Header_TSO_Details VARCHAR(MAX), Details_EntryDate DATE, Details_Notes VARCHAR(MAX)) CREATE TABLE CSR_TrackingReport (Header_Customer_Feedback VARCHAR(MAX), Details_IncidentDate DATE, Details_CustomerFeedback VARCHAR(MAX), InvestigationSummary_Investigator VARCHAR(50), InvestigationSummary_InvestigationNotes VARCHAR(MAX)) CREATE TABLE AbsenceRequest (Details_Requestor VARCHAR(50), Details_RequestState VARCHAR(10))
Or...any other suggestions besides creating tables would be welcomed too.