Concatenate issue - 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

General SQL Server Forums

Database Design and Application Architecture

Concatenate issue

Author

Topic

natfresh
Starting Member

5 Posts

Posted - 2008-07-17 : 15:17:02

I need to concatenate some data from a table with the following data:
Field1 Field2 Field3
------ ------ ------------
1 | Joe | Schools
1 | Joe | Colleges
1 | Joe | Universities

to get the following: 1-Joe, Schools, Colleges, Universities

as a single result set. This has been confusing and frustrating for me. Any help would be appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-17 : 15:28:31

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-17 : 15:57:58

The link above still applies.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-17 : 16:02:33

[code]DECLARE @Sample TABLE (Field1 INT, Field2 VARCHAR(10), Field3 VARCHAR(20))

INSERT @Sample
SELECT 1, 'Joe', 'Schools' UNION ALL
SELECT 1, 'Joe', 'Colleges' UNION ALL
SELECT 1, 'Joe', 'Universities'

SELECT f1f2.Item + y.f3 AS Result
FROM (
SELECT Field1,
Field2,
CAST(Field1 AS VARCHAR(12)) + '-' + Field2 AS Item
FROM @Sample
GROUP BY Field1,
Field2
) AS f1f2
CROSS APPLY (
SELECT ', ' + w.Field3
FROM @Sample AS w
WHERE w.Field1 = f1f2.Field1
AND w.Field2 = f1f2.Field2
FOR XML PATH('')
) AS y(f3)[/code]

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

natfresh
Starting Member

5 Posts

Posted - 2008-07-17 : 16:07:39

Thank you. I will try that. I was confused by which code portion you were referencing.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-17 : 16:15:13

And you didn't copy and test them?

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

natfresh
Starting Member

5 Posts

Posted - 2008-07-17 : 16:20:05

I was in the process of working on the first set, and modofying for my use. I just needed a nudge and I generally I can find my way. Thanks.

natfresh
Starting Member

5 Posts

Posted - 2008-07-17 : 16:25:49

I created a temp table with the fields:

JoeID, JoeName, JoeDESC

This follows the same as Field1, Field2, Field3 above.

I then modified the query as follows:

--DECLARE	@Sample TABLE	(Field1 INT, Field2 VARCHAR(10), Field3 VARCHAR(20))

--INSERT	@Sample
--SELECT	1, 'Joe', 'Schools' UNION ALL
--SELECT	1, 'Joe', 'Colleges' UNION ALL
--SELECT	1, 'Joe', 'Universities'

SELECT		f1f2.Item + y.f3 AS Result
FROM		(
			SELECT	JoeID,
					JoeNAME,
					CAST(JoeID AS VARCHAR(12)) + '-' + JoeNAME AS Item
			FROM		Table_JOE_TEST_072008
			GROUP BY	JoeID, JoeNAME
		) AS f1f2
CROSS APPLY	(
			SELECT	', ' + w.JoeDESC
			FROM		Table_JOE_TEST_072008 AS w
			WHERE	w.JoeID = f1f2.JoeID
				AND w.JoeNAME = f1f2.JoeNAME
			FOR XML	PATH('')
		) AS y(f3)

and I get the following error:

Msg 170, Level 15, State 1, Line 16
Line 16: Incorrect syntax near 'APPLY'.
Msg 156, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'FOR'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-17 : 16:26:51

CROSS APPLY is a new operator for SQL Server 2005 and needs to have database compatibility set to 90.

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

natfresh
Starting Member

5 Posts

Posted - 2008-07-17 : 16:38:18

That my friend was the answer. Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources