Concat truncates in SQL 2005

Question

Concat truncates in SQL 2005

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi

I got it. You have to specify an alias for the outer table. Since your table name is "holddups" and you refer it within your sub-query you cannot specify "NUM1 = holddups.NUM1". This refers to itself. Try this:

USE tempdb

SELECT

NUM1,

STUFF(

(

SELECT ',' + SERVIZI

FROM holddups

WHERE NUM1 = h1.NUM1

FOR XML PATH(''))

, 1, 1, '')

FROM holddups h1

GROUP BY NUM1

Greets

Flo

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Florian Reischl (5/7/2009)
Hi
I got it. You have to specify an alias for the outer table. Since your table name is "holddups" and you refer it within your sub-query you cannot specify "NUM1 = holddups.NUM1". This refers to itself. Try this:
USE tempdb
SELECT
NUM1,
STUFF(
(
SELECT ',' + SERVIZI
FROM holddups
WHERE NUM1 = h1.NUM1
FOR XML PATH(''))
, 1, 1, '')
FROM holddups h1
GROUP BY NUM1
Greets
Flo

Good catch Flo. I missed that one myself. I starting thinking about possible data issues.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

Lynn Pettis (5/7/2009)
Good catch Flo. I missed that one myself. I starting thinking about possible data issues.

Thanks Lynn! Did something likewise today at work and saw that something seems to be missing.

VoodooGodM SSC Enthusiast Points: 131 More actions · Answer 4

Lynn/Flo,

Thanks for all your help, the query works the table is now down to 5000 rows instead of 1M.

Des.