SQL Query with normalized user defined values into a single table

psdavis
psdavis used Ask the Experts™
on
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,
                     etc...
 
[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

Comment
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

Commented:
Can you tell me what's your question here?
See Mark's article on the topic:
http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Dynamic-Pivot-Procedure-for-SQL-Server.html

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.

Author

Commented:
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!

Author

Commented:
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.

Author

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

Commented:
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!!!

Author

Commented:
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

Commented:
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?

Author

Commented:
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,
               C.*,
          (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