SQLTeam.com | Weblogs | Forums

Concat multi row


#1

Hi all,
I have table as :
Now i want to concat multi row based on "groupquestionId"
Output desired :
QuestionID ExamID GroupQuestionID
1,3,7 ----------- 1 ----------1
8,4,6----------- 1-----------10000
2,9----------- 1-----------2
10,5----------- 1---------- 3

How can I do this ?
Thank you so much !


#2

Here's my template for creating a concatenated, comma-delimited, list from a Child Table:

SELECT	H.HeaderCol1
	, H.HeaderCol2
	, [MyValueList] = STUFF(
		(
			SELECT	',' + C.ColToList
-- ** Use COALESCE(C.ColToList, '') to preserve values that can be NULL
-- ** OR **
			SELECT	',' + CONVERT(varchar(20), C.NumericColToList)
			FROM	dbo.ChildTable AS C
			WHERE	C.MyKey = H.MyKey
			ORDER BY C.C_ID
-- ** And / Or use the column itself for the delimited list to be "in order":	, C.ColToList
			FOR XML PATH(''), TYPE
		).value('.', 'varchar(max)')	-- NOTE: "'value" is case sensitive!!
	        , 1, 1, '')
-- ** For a 2 character delimiter e.g. ", " use:	, 1, 2, '')
FROM	dbo.HeaderTable AS H

#3

It's quite difficult to apply your template into my data ? Can you check your template and give me clear explaination ?

Thank you so much !


#4

It works much better if you post a CREATE TABLE statement, some INSERT statements that populate it with some sample data as well as your expected output (and any query that you have tried)

If you just post an image then I, and everyone else who might like to help you, will have to type all that in from scratch. I don't have the time, and given noone else has answered (and what I have seen on other posts where folk just post an image) they probably don't have time either.

So that's why I posted a generic template answer, hoping it would give you something to have a go with.


#5

I am posting below sql, replace column names and table names:

 select
   distinct  
    stuff((
        select ',' + u.username
        from users u
        where u.username = username
        order by u.username
        for xml path('')
    ),1,1,'') as userlist
from users
group by username

Let me know if you find any dificulty.