question

Robert Morgan avatar image
Robert Morgan asked

Hi I'm using the below code to concatenate strings but am getting duplicate rows.

 STUFF((Select DISTINCT ','+T1.VSCTDATA                --added cancellation comments
						from PAS.VISCMTAF T1
						where T1.VSCTVIST=T8.VSCTVIST 
						FOR XML PATH('')),1,1,'')  COMMENT

 Where strings from 2 or more rows are concatenated then I get 2 or more identical rows in the main query
tsql
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I should add that the table has no primary key and the T1.vsctvist is the field used to join to other tables. This field will contain duplicates if there are more than 1 line of comments.

0 Likes 0 ·

1 Answer

· Write an Answer
Robert Morgan avatar image
Robert Morgan answered

resolved

 COMMENT AS (
			SELECT DISTINCT COMMENT,VSCTVIST FROM 
				(SELECT T8.VSCTVIST,
						 STUFF((Select DISTINCT ','+T1.VSCTDATA               --added cancellation comments
						from PAS.VISCMTAF T1
						where T1.VSCTVIST=T8.VSCTVIST 
						FOR XML PATH('')),1,1,'') COMMENT
					FROM PAS.VISCMTAF T8)SUB)

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.