Link to home
Start Free TrialLog in
Avatar of psdavis
psdavisFlag for United States of America

asked on

SQL Query with normalized user defined values into a single table

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

Avatar of Daniel Wilson
Daniel Wilson
Flag of United States of America image

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.
Avatar of Sharath S
Can you tell me what's your question here?
ASKER CERTIFIED SOLUTION
Avatar of Daniel Wilson
Daniel Wilson
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of psdavis

ASKER

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!
Avatar of psdavis

ASKER

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.
Avatar of psdavis

ASKER

I'm betting that you're right.  Thanks for the help Daniel!
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!!!
Avatar of psdavis

ASKER

Sharath, if you have a different answer, I can reopen the question and award more points.  I still have around 10k points to spend.
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?
Avatar of psdavis

ASKER

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