How to use set-based SQL in my query

Question

I have a database set up to store all the input from every web form that I have created that looks like this:

(FormData)
----------------------
(PK) Id int not null,
(FK) FormFieldId int not null,
(FK) FormId int not null,
     Data varchar(max) null,
     DateSubmitted datatime not null,
(FK) SubmissionId uniqueidentifier not null

I need to query this database to select the fields that are specific to a web form and store them in a separate table called TSERequests. Since the FormFieldIds are specific to the form, there is no need to worry about the FormId. To select all the data that I need, I am using a simple query:

SELECT Data, FormFieldId,
DENSE_RANK() OVER (ORDER BY DateSubmitted desc) AS RequestRanking
FROM FormData
WHERE FormFieldId IN (191, 192, 193, 194, 195, 196, 197, 198, 205, 208, 213, 
216, 217, 218, 219, 220, 242, 243, 244, 269)

This query gives me a result like this:

(Data)       |     (FormFieldId)    |    (RequestRanking)
-------------+----------------------+--------------------
TestData1    |     191              |    1
TestData2    |     192              |    1
TestData3    |     193              |    1
   ...       |     ...              |   ...
TestData20   |     269              |    1
TestData21   |     191              |    2
TestData22   |     192              |    2
TestData23   |     193              |    2

The RequestRanking increments for every new request that it finds. I know that I could use a cursor or while loop to loop through every RequestRanking and individually set each piece of data to the corresponding field in my new table, but we all know how long this would take.

I am still very new to SQL and I need some help. I was wondering if there is a simple and efficient way to execute this using set-based SQL instead of procedural SQL.

EDIT: To be clear, this is what TSERequests looks like (the table that I am trying to insert into)

(TSERequests)
-----------------------------
(PK) RequestId int not null,
     DateCreated date not null,
     Name varchar(100) not null,
     StreetAddress varchar(100) null,
     City varchar(100) null,
     State int null,
     ZipCode varchar(10) null,
     PhoneNumber varchar(15) null,
     EmailAddress varchar(100) null,
     RequestStartDate date not null,
     RequestEndDate date not null,
     RequestStatus int null

Each column in this table has a corresponding FormFieldId in the FormData table. For example, the FormFieldId (191) in the FormData table corresponds to the column (DateCreated) in the TSERequests table.

Answer 1

I believe you want to present your data so the all the fields with the same dense rank will be placed in a single row in your target table, and the fields will be listed as columns. If so, then a PIVOT will accomplish this.

;WITH myDataSet AS
(
    SELECT Data, FormFieldId,
    DENSE_RANK() OVER (ORDER BY DateSubmitted desc) AS RequestRanking
    FROM FormData
    WHERE FormFieldId IN (191, 192, 193, 194, 195, 196, 197, 198, 205, 208, 213, 
    216, 217, 218, 219, 220, 242, 243, 244, 269)
)
SELECT
    RequestRanking, [191], [192], [193], [194], [195], [196], [197], [198], [205], [208], [213], 
    [216], [217], [218], [219], [220], [242], [243], [244], [269]
FROM
(
    SELECT data, FormFieldId, RequestRanking From myDataSet ) as sourceData

    PIVOT
    (
        Max(data)
        FOR FormFieldID in ([191], [192], [193], [194], [195], [196], [197], [198], [205], [208], [213], 
                            [216], [217], [218], [219], [220], [242], [243], [244], [269])
    ) AS PivotTable