July 1, 2014 at 5:12 pm
Hi
I need to produce an output from a survey table which has an answer column that contains one or multiple values from a multi-pick list of answers. From the sample data below I require an output which looks like this...
REQUIRED OUTPUT
surveyName respondent filmPreference
filmsurvey Chuck Henry Comedy, Action, Documentary
filmsurvey Suzie Lopez Romance
filmsurvey Brad Murray SciFi, Westerns
filmsurvey Bill Cooper Comedy, Drama, Action, Animated
filmsurvey Tyler Fitch Romance
The column named filmPreference needs to contain the options selected by the respondent and if there are several answers these need to be separated by commas.
SAMPLE DATA
IF OBJECT_ID('tempdb..##multiPick') IS NOT NULL DROP TABLE ##multiPick
CREATE TABLE ##multiPick (
[surveyName] [varchar] (100) NULL,
[respondent] [varchar] (20) NULL,
[filmPreference] [varchar](20) NULL
) ON [PRIMARY]
GO
INSERT INTO ##multiPick (surveyName, respondent, filmPreference) VALUES
('filmsurvey','Chuck Henry','Comedy'),
('filmsurvey','Chuck Henry','Action'),
('filmsurvey','Chuck Henry','Documentary'),
('filmsurvey','Suzie Lopez','Romance'),
('filmsurvey','Brad Murray','SciFi'),
('filmsurvey','Brad Murray', 'Westerns'),
('filmsurvey','Bill Cooper','Comedy'),
('filmsurvey','Bill Cooper','Drama'),
('filmsurvey','Bill Cooper','Action'),
('filmsurvey','Bill Cooper', 'Animated'),
('filmsurvey','Tyler Fitch','Romance')
Any help would be most appreciated
🙂
July 2, 2014 at 1:10 am
Hi,
please try below
SELECT DISTINCT [surveyName],[respondent],
(STUFF(
(SELECT ',' + filmPreference
FROM ##multiPick B
WHERE A.respondent = B.respondent
FOR XML PATH ('')), 1, 1, '') ) filmPreference
FROM ##multiPick A
July 2, 2014 at 7:26 am
Awesome!
I would never have thought of using the XML features within SQL
I'm really grateful for this.
:-):-)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply