psdavis
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!
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
Can you tell me what's your question here?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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]
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!
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'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.
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.
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!!!
Good to know that you got your answer from Mark's article. Have a nice day!!!
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?
Do you still want me to look into this question?
ASKER
Daniel's suggestion worked very well.
declare @columns varchar(max)
declare @sql nvarchar(max)
set @sql = N'set @columns = substring((select '', [''+convert(varchar,Additi onalFieldI d)+'']'' 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
declare @columns varchar(max)
declare @sql nvarchar(max)
set @sql = N'set @columns = substring((select '', [''+convert(varchar,Additi
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
Or do you need it totally dynamic -- whatever's there should show? If so, you need some dynamic SQL.