MULTIPLE RECORD in one row

[borgkling2003]

Hi. Can someone show me how to concatenate multiple record into one row. Currently the query below returns something like:


3478|C
3478|A
3490|A
3490|B
3490|C

But I want it to return like this:


3478|C, A
3490|A, B, C


How do I do it?


SELECT
CUST_ID,
max(case when QUESTION_NUM = 'Q10' then ANSWER_DESC end) 'Q10'

FROM
(SELECT
[MS_NEWS].[dbo].[SURVEY_RESPONSE].R_ID AS CUST_ID, [MS_NEWS].[dbo].[SURVEY_RESPONSE].ID AS IDD, SUBSTRING([MS_SURVEYCENTRAL].[dbo].[QUESTION].Q_TEXT,1,charindex('.',[MS_SURVEYCENTRAL].[dbo].[QUESTION].Q_TEXT)-1) as QUESTION_NUM , [MS_SURVEYCENTRAL].[dbo].[QUESTION].Q_TEXT as QUESTION_DESC , [MS_SURVEYCENTRAL].[dbo].[ANSWER].A_TEXT as ANSWER_DESC
FROM
[MS_NEWS].[dbo].[SURVEY_RESPONSE]
INNER JOIN
[MS_SURVEYCENTRAL].[dbo].[QUESTION]
ON [MS_NEWS].[dbo].[SURVEY_RESPONSE].Q_ID = [MS_SURVEYCENTRAL].[dbo].[QUESTION].Q_ID
AND [MS_NEWS].[dbo].[SURVEY_RESPONSE].Q_ID = [MS_SURVEYCENTRAL].[dbo].[QUESTION].Q_ID
AND [MS_NEWS].[dbo].[SURVEY_RESPONSE].Q_ID=150
INNER JOIN
[MS_SURVEYCENTRAL].[dbo].[ANSWER]
ON [MS_NEWS].[dbo].[SURVEY_RESPONSE].A_ID = [MS_SURVEYCENTRAL].[dbo].[ANSWER].A_ID) HIA_PIVOT_RESULT
group by CUST_ID, IDD

 

[mrdenny]

You need to use a function to do this.

Pass the function the CUST_ID and have it find all the ANSWER_DESC and put them together in a VARCHAR variable.

There are several examples in the forums.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com