quote:

---

Originally posted by itsme3112

How can I get distinct?

Function workds great.
One problem i am facing..

I have duplicate in the column I want to concat
But I want result to be distinct.. How do I change the Function?

example:
1 a
1 b
1 c
1 a

I want the result to be
1 a,b,c

TIA

---

CREATE FUNCTION dbo.ConcatAuthors(@State CHAR(2))
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE @Output VARCHAR(8000)
	SET @Output = ''

	SELECT @Output =	CASE @Output 
				WHEN '' THEN au_lname 
				ELSE @Output + ', ' + au_lname 
				END
	FROM (select distinct au_lname from Authors) T
	WHERE State = @State
	ORDER BY au_lname

	RETURN @Output
END
GO

quote:

---

Originally posted by eralper

Hello,

You can review the article titled "Get column values as comma seperated list using XML PATH() instead of UDF's using COALESCE" at http://www.kodyaz.com/articles/concatenate-using-xml-path.aspx

I'm also copying down a sample script that might help you.

SELECT
distinct Id,
STUFF(
(
SELECT distinct ',' + name
FROM table2 t
WHERE t.id = tt.id
FOR XML PATH('')
), 1, 1, '') as names
FROM table2 tt

Eralper



-------------
Eralper
http://www.kodyaz.com

---

quote:

---

Originally posted by eralper

Hello,

You can review the article titled "Get column values as comma seperated list using XML PATH() instead of UDF's using COALESCE" at http://www.kodyaz.com/articles/concatenate-using-xml-path.aspx

---