SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 concatenation
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

itsme3112
Starting Member

1 Posts

Posted - 08/02/2007 :  16:42:09  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 08/03/2007 :  03:03:48  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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



Modified function code from http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true


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


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

eralper
Yak Posting Veteran

66 Posts

Posted - 05/13/2008 :  04:09:47  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/13/2008 :  04:15:05  Show Profile  Reply with Quote
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



But you should have SQL 2005 with compatibility mode set to 90 in order to use FOR XML PATH
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30276 Posts

Posted - 05/13/2008 :  04:23:26  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Nice explanation of the examples found here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

But that is not the point. The point is that this is a SQL Server 2000 topic.


E 12°55'05.25"
N 56°04'39.16"

Edited by - SwePeso on 05/13/2008 04:25:11
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000