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.

I'm not clear on what you're asking. Do you need an `INSERT` or `UPDATE` to `FormData` based on the query you've given? If so, you can just write one -- both support `FROM` and joins. (But this would be a self-join, so using a common table expression is even more convenient.) — Jeroen Mostert, Jul 19 '17 at 14:07
I need an INSERT to the TSERequests table. Every FormFieldId in FormData has a corresponding column in TSERequests. — A.Stekl, Jul 19 '17 at 14:11
I'll edit my post to show what my other database looks like. — A.Stekl, Jul 19 '17 at 14:16
Thanks, that edit was *very* much needed to understand what you're really after. — Jeroen Mostert, Jul 19 '17 at 14:28

Ryan B. · Accepted Answer · 2017-07-19T14:27:35.780

2

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

edited Jul 19 '17 at 14:27

answered Jul 19 '17 at 14:21

Ryan B.

3,575
2
20
26

Thank you for your response. I have tried using a pivot, but the problem that I ran into was that if one of the values happens to be null, the FormFieldIds don't match up to the right columns in TSERequests. – A.Stekl Jul 19 '17 at 14:27
What version of SQL? The resulting pivot should list out a column for every value in the select list, even if everything is null. I'm not sure why that would map out inconsistently to the target. – Ryan B. Jul 19 '17 at 14:36
I figured out my problem. I was trying to pivot using ROW_NUMBER() instead of FormFieldId. Your answer works perfectly. Thank you very much! – A.Stekl Jul 19 '17 at 15:00

How to use set-based SQL in my query

1 Answers1