Set based approach to 'pivoting' rows into columns

Question

Set based approach to 'pivoting' rows into columns

kangarolf

Hall of Fame

Points: 3432
More actions
March 20, 2012 at 3:16 am

#73294

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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

The following two articles will teach you how to handle just about any situation. For character based columns, use MAX instead of SUM.

http://www.sqlservercentral.com/articles/T-SQL/63681/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)