query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

query

Author

Topic

swatib
Posting Yak Master

173 Posts

Posted - 2006-09-19 : 05:46:29

i have a query

ACTIVITY- SUBACTIVITY
A-A1
A-A2
A-A3
B-B2

expected Result :
A-A1,A2,A3
B,B2

Njoy Life

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-19 : 05:50:29

See this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53293

And if you don't get it, try this

CREATE FUNCTION	dbo.ConcatRows
(
	@Activity VARCHAR(10)
)
RETURNS VARCHAR(8000)
AS

BEGIN
	DECLARE @Output VARCHAR(8000)

	SELECT		@Output = COALESCE(@Output+', ', '') + CONVERT(VARCHAR(20), at.SubActivity)
	FROM		ActivityTable at
	WHERE		at.Activity = @Activity
	ORDER BY	at.AubActivity

	RETURN	@Output
END
Go

SELECT	DISTINCT Activity,
	dbo.ConcatRows(Activity)
FROM	ActivityTable

Peter Larsson
Helsingborg, Sweden

swatib
Posting Yak Master

173 Posts

Posted - 2006-09-19 : 08:25:46

what is the @Activity in the function?

Njoy Life

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-19 : 08:33:47

@Activity is the parameter used to concat your data.
You send the first column to the function and name it @Activity in the function.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources