sql help with getting data

Question

I have a table called Forms with fields ID, FormName and a couple of other fields.

I have another table called FormFields which has the FormId as a foreign key as well as an ID and a FieldName and FieldValue fields.

The idea is that this should be able to save and retrieve data for generic forms.

How can i get all the data from the Forms table and have all the data relevant to a the right FormId?

E.g. I want it to return the row with a column "FormData" that can store all the data from the FormFields table.

I'm using SQL Server

Answer 1

I believe what you need is a dynamic pivot operation. Since SQLServer pivot needs statically named columns in order to do its work, I suggest constructing the query dynamically.

A good example of this can be found here.

Hope this helps.

Answer 2

You do not want to store form data (data that someone fills into a form) in the same table that stores form schema (the data that describes your form). Conceptually, this is the same distinction as that between a class and an instance: the form schema is like a class, a filled-in form is like an instance of that form.

So, you have a table that lists forms and a table that lists fields on those forms. Great. To get the field schema for form @FormID:

Select    *
From      FormFields
Where     FormID = @FormID

Presumably you then render the form in some way and allow the user to populate the fields. When saving, you should save this data somewhere else. You cold save this data in another set of tables:

Table:   FormInstances
    Field: ID int - form instance ID
    Field: FormID int - link to table FormTable
    Field: Name varchar(50) - a short descriptive name of this instance (e.g. Bobs expenses for 1/1/2010)
    Field: Created datetime
    Field: Edited datetime
    etc.

Table:   FormFieldData
    Field: ID int
    Field: FieldID int - link to table FormFields
    Field: Value (this is the problem with an abstract system like this - what data type to use for the field values)
    etc.

However, you're much better off using files to save your form schema and your form content while using a database to manage your forms. XML was created for this sort of thing (q.v. XForms).