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
 General SQL Server Forums
 Script Library
 How to concatenate records without UDF		  New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

adalgeir
Starting Member

4 Posts
Posted - 12/23/2009 :  05:21:46  Show Profile  Reply with Quote
Thanks for the help guys - really appreciate it! When I was about to test this I realised that the tables are stored in an Oracle DB. :( This particular system stores data within couple of databases and I was so sure that these set of tables were within the SQL Server - which they weren't. Sigh..
Go to Top of Page

adalgeir
Starting Member

4 Posts
Posted - 12/23/2009 :  05:22:37  Show Profile  Reply with Quote
Sorry - wrong topic!
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 09/28/2010 :  12:35:06  Show Profile  Reply with Quote
a "who's is biger": argument....

XML bites



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx
Go to Top of Page

matju
Starting Member

3 Posts
Posted - 01/28/2011 :  07:58:30  Show Profile  Reply with Quote
U can use FOR XML PATH or UDF.
Full code: http://biblog.pl/?p=140
Go to Top of Page

denfer
Starting Member

12 Posts
Posted - 03/31/2011 :  05:57:25  Show Profile  Reply with Quote
Hello
if I have let say TaskID like in the sample:

DECLARE @Sample TABLE (TaskID INT, ID INT, Code VARCHAR(3))

INSERT @Sample
SELECT 1, 290780, 'LT' UNION ALL
SELECT 2, 290780, 'AY' UNION ALL
SELECT 3, 290781, 'ILS' UNION ALL
SELECT 4, 290780, 'AY'

How can I have concatenated list of TaskID like below?
290780 AY,LT 1,2,4
290781 ILS 3

thanks in advance
Olivier
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 03/31/2011 :  07:13:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Using double concatenations?
SELECT		s.ID,
		STUFF(c.Data, 1, 1, '') AS Codes,
		STUFF(t.Data, 1, 1, '') AS Tasks
FROM		(
			SELECT	DISTINCT
				ID
			FROM	@Sample
		) AS s
CROSS APPLY	(
			SELECT DISTINCT	',' + x.Code
			FROM		@Sample AS x
			WHERE		x.ID = s.ID
			ORDER BY	',' + x.Code
			FOR XML		PATH('')
		) AS c(Data)
CROSS APPLY	(
			SELECT DISTINCT	',' + CAST(x.TaskID AS VARCHAR(12))
			FROM		@Sample AS x
			WHERE		x.ID = s.ID
			ORDER BY	',' + CAST(x.TaskID AS VARCHAR(12))
			FOR XML		PATH('')
		) AS t(Data)


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

denfer
Starting Member

12 Posts
Posted - 03/31/2011 :  07:50:29  Show Profile  Reply with Quote
Thanks so much for your help
I have never used the Cross Apply before and it seems very interesting
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 03/31/2011 :  09:18:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You can use a correlated subquery too in this case.
I think CROSS APPLY is easier to read.


N 56°04'39.26"
E 12°55'05.63"
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.06 seconds. Powered By: Snitz Forums 2000