SQL Query with normalized user defined values into a single table

psdavis used Ask the Experts™
I'm a good enough of a C# programmer to know that there's always at least five ways to do something in SQL, so I'm going to throw this out to the SQL experts instead.

I have a simple 'Submission' table that returns basic information.  [Table 1]

The database can contain additional user defined fields to the submission table via a table called 'AdditionalFields'.  It only contains a key and a 'Mnemonic' field.  [Table 2]

When a record is saved, the user defiined values are stored in the 'SubmissionFields' table.   [Table 3]

The first output has pivoted the values in the AdditionalFields table into column names.  [Table 4]

The final solution merges the columns of Submission table [Table 1] with the data in [Table 4] to create  [Table 5]

I hope the data makes the question clear.  Thanks for your help!
CREATE TABLE [dbo].[Submissions](
	[SubmissionId] [int] IDENTITY(1,1) NOT NULL,
	[PeopleId] [int] NULL,
[Table 1]
SubmissionId  PeopleId   
 ------------  --------
42            212
97            492
CREATE TABLE [dbo].[AdditionalFields](
	[AdditionalFieldId] [int] IDENTITY(1,1) NOT NULL,
	[Mnemonic] [nvarchar](50) NULL
[Table 2]
AdditionalFieldId       Mnemonic
-----------------       ------------
1                       PlaceOfBirth
2                       SocialSecurityNumber
3                       MotherMaidenName
CREATE TABLE [dbo].[SubmissionFields](
	[SubmissionFieldId] [int] IDENTITY(1,1) NOT NULL,
	[SubmissionId] [int] NOT NULL,
	[AdditionalFieldId] [int] NOT NULL,
	[Value] [nvarchar](50) NOT NULL
[Table 3]
SubmissionFieldId    SubmissionId    AdditionalFieldId   Value
-----------------    --------------  -----------------   ---------
1                    42              1                   TX
2                    42              2                   445129321
3                    42              3                   Smith
4                    97              1                   OK
5                    97              3                   Jones
[Table 4]
SubmissionId   PlaceOfBirth    SocialSecurityNumber    MotherMaidenName
------------   -------------   --------------------    ----------------
42             TX              445129321               Smith
97             OK             null                     Jones
[Table 5]
SubmissionId  PeopleId  PlaceOfBirth    SocialSecurityNumber    MotherMaidenName
------------  --------  -------------   --------------------    ----------------
42            212       TX              445129321               Smith
97            492       OK             null                     Jones

Open in new window

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Do you want only certain mnemonics that you can hard-code included?  If so, we can hard-code a PIVOT statement.

Or do you need it totally dynamic -- whatever's there should show?  If so, you need some dynamic SQL.
SharathData Engineer

Can you tell me what's your question here?
See Mark's article on the topic:

His procedure does the dynamic stuff.  The code is not pretty ... but the result is!
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.


Sharath, The question is...
What is the SQL command to use to generate [Table 4] and [Table 5]
The PIVOT command cannot be hard coded.  The column names are going to be retrieved from the database [Table 2]
yeah, see Mark's article.  I wish i could say I wrote the script there ... except I really didn't want to sit down and write that one!


I'll check it out tomorrow morning at work and see if this solution gives me the results that I'm after.
I'm now wondering if there's a better solution using ADO.NET....?
I don't know all the tricks ADO.NET has to offer ... but ... I suspect Mark's is the best answer.

My rule is that data manipulation should be done in the DB.  Formatting is a client-side (e.g. ADO.Net or probably at the form level) issue.


I'm betting that you're right.  Thanks for the help Daniel!
SharathData Engineer

By the time, i am looking into this question again, its got closed.

Good to know that you got your answer from Mark's article. Have a nice day!!!


Sharath, if you have a different answer, I can reopen the question and award more points.  I still have around 10k points to spend.
SharathData Engineer

I didn't even look into this question once its got closed. As you know, there are so many pending questions :)

Do you still want me to look into this question?


Daniel's suggestion worked very well.

   declare @columns varchar(max)
   declare @sql nvarchar(max)
   set @sql = N'set @columns = substring((select '', [''+convert(varchar,AdditionalFieldId)+'']'' from SubmissionFields group by AdditionalFieldId for xml path('''')),2,8000)'
   EXECUTE sp_executesql @sql, N'@columns varchar(max) output', @columns=@columns output
   set @sql =
        N'WITH C AS
            SELECT * FROM
            ( SELECT SubmissionId, AdditionalFieldId, Value from SubmissionFields ) src
            PIVOT( max( Value ) FOR AdditionalFieldId IN (' + @columns + ' )) pvt
        SELECT A.TcnNumber, A.CreatedDate, A.FileName, A.MissionId, A.IsEditLater,
               B.PeopleId, B.FirstName, B.MiddleName, B.LastName,
          (SELECT     COUNT(*)
            FROM          dbo.Responses
            WHERE      (A.SubmissionId = SubmissionId)) AS ResponseCount,
          (SELECT     ISNULL(MAX(HitIndicator), 0)
            FROM          dbo.Responses
            WHERE      (A.SubmissionId = SubmissionId)) AS HitIndicator      
        FROM    Submissions A
        INNER JOIN C ON A.SubmissionId = C.SubmissionId
        INNER JOIN People B ON A.PeopleId = B.PeopleId
        WHERE A.MissionId = ' + CAST( @missionId as NVarchar ) + '
        AND A.IsEditLater = ' + CAST( @isEditLater as NVarchar )
   EXECUTE sp_executesql @sql

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial