March 20, 2012 at 3:16 am
Hi all,
I have been adding the ability to use custom form fields on a web application. These are stored in a table along with some XML data describing how to render them.
I would like to return data on people along with their responses to custom form fields as columns. There maybe multiple custom fields with responses for each person.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#PEOPLE','U') IS NOT NULL
DROP TABLE #PEOPLE
CREATE TABLE #PEOPLE (
[PERSONID] [int] IDENTITY (1, 1) NOT NULL ,
[REGISTRATIONDATE] [smalldatetime] NOT NULL
) ON [PRIMARY]
SET IDENTITY_INSERT #PEOPLE ON
INSERT INTO #PEOPLE (PERSONID, REGISTRATIONDATE)
SELECT 1,'01/01/2012'
SET IDENTITY_INSERT #PEOPLE OFF
IF OBJECT_ID('TempDB..#CUSTOM','U') IS NOT NULL
DROP TABLE #CUSTOM
CREATE TABLE #CUSTOM (
[CUSTOMRESPONSEID] [int] IDENTITY (1, 1) NOT NULL ,
[PERSONID] [int] NOT NULL ,
[CUSTOMID] [int] NOT NULL ,
[RESPONSE] varchar(150) NOT NULL
) ON [PRIMARY]
SET IDENTITY_INSERT #CUSTOM ON
INSERT INTO #CUSTOM (CUSTOMRESPONSEID, PERSONID, CUSTOMID, RESPONSE)
SELECT 1,1,1,'Blue' UNION ALL
SELECT 2,1,2,'Dogs'
SET IDENTITY_INSERT #CUSTOM OFF
SELECT * FROM #PEOPLE P INNER JOIN #CUSTOM C ON P.PERSONID = C.PERSONID
DROP TABLE #CUSTOM
DROP TABLE #PEOPLE
A normal join will of course produce multiple rows for each person. In the above example there is one person with a response of BLUE to the first custom field and a response of DOGS to the second.
Thanks in advance for any advice.
Rolf
March 20, 2012 at 6:47 am
The following two articles will teach you how to handle just about any situation. For character based columns, use MAX instead of SUM.
http://qa.sqlservercentral.com/articles/T-SQL/63681/
http://qa.sqlservercentral.com/articles/T-SQL/63681/
--Jeff Moden
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply