Concatenate column(s) of a duplicate

Question

Post reply

Concatenate column(s) of a duplicate

jay.jose

Say Hey Kid

Points: 683
More actions
November 2, 2009 at 3:56 pm

#216956

Hi all,
I'm trying to come up with a query that will concatenate a column(s) of multiple records that are having the same value from a certain column. For example, i have the following on a table
DATE TIME EVENT SPEAKER
03/01/2009 09:30 AM CIP Mr. John Doe
03/01/2009 03:30 PM CIP Mr. John Doe
03/02/2009 09:30 AM CIFT Mr. John Doe
03/03/2009 10:00 AM CIP Mr. John Doe
03/05/2009 02:00 PM CIFT Mr. John Doe
03/05/2009 07:00 PM CIFT Mr. John Doe
What i need is a query that will give the following result
DATE TIME EVENT SPEAKER
03/01/2009 09:30 AM & 03:30 PM CIP Mr. John Doe
03/02/2009 09:30 AM CIFT Mr. John Doe
03/03/2009 10:00 AM CIP Mr. John Doe
03/05/2009 02:00 PM & 07:00 PM CIFT Mr. John Doe
Thanks in advance for any help and suggestions

Viewing 11 posts - 1 through 10 (of 10 total)

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

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 1

Try this link

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Clear Sky SQL
My Blog[/url]

jay.jose Say Hey Kid Points: 683 More actions · Answer 2

Dave Ballantyne (11/2/2009)
Try this link
http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Thanks Dave,

Now my problemn is that i need to escape the ampersand '&' on the concatenated TIME string...i have tried set escape '\' and set define off. They are always producing an error like Incorrect syntax near the keyword 'escape'. or 'define' is not a recognized SET option. how can i escape the '&' being converted to '&'?

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 3

Dave Ballantyne

SSC-Dedicated

Points: 33566

November 2, 2009 at 11:39 pm

#1074118

try using '&'

Clear Sky SQL
My Blog[/url]

jay.jose Say Hey Kid Points: 683 More actions · Answer 4

Dave Ballantyne (11/2/2009)
try using '&'

Actually I'm trying to concatenate the ampersand to join 2 values like

select

@TIME1 + ' & ' + @TIME2

but it is always producing

'09:30 AM & a m p; 02:30 PM'

i have tried using '/', '\' or ' " ' before or after the '&' sign, but still not working. It just adds '\', '/' or ' " ' before or after the '& a m p;'

**the wysiwyg is converting the & a m p; to & so i put spaces between them

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 5

jay.jose (11/3/2009)

**the wysiwyg is converting the & a m p; to & so i put spaces between them

I didnt notice that

so in you sql use

select

@TIME1 + ' & a m p; ' + @TIME2

again with the spaces.

Clear Sky SQL
My Blog[/url]

jay.jose Say Hey Kid Points: 683 More actions · Answer 6

Dave Ballantyne (11/3/2009)

jay.jose (11/3/2009)
**the wysiwyg is converting the & a m p; to & so i put spaces between them
I didnt notice that
so in you sql use
select
@TIME1 + ' & a m p; ' + @TIME2
again with the spaces.

Still not working, the result would be

09:30 AM & a m p;amp; 02:30 PM

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

Not sure as the following works just as I would expect:

select 'A' + ' & ' + 'B'

jay.jose Say Hey Kid Points: 683 More actions · Answer 8

Thanks Lynn,

I have tried the concatenation with ampersand on a clean query and it works fine. I went back to my query and tried it on other parts and it's also working good. The only place that it is not working is on the concatenation of the column with duplicate records. I think the 'for xml path('')' is the one messing the ampersand concatenation. Any ideas on how to fix it? or should I start over and use a different approach for the concatenation of the column with duplicate records?

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 9

Dave Ballantyne (11/3/2009)

select
@TIME1 + ' & a m p; ' + @TIME2
again with the spaces.

Sorry that should be WITHOUT the spaces

Clear Sky SQL
My Blog[/url]

jay.jose Say Hey Kid Points: 683 More actions · Answer 10

Dave Ballantyne (11/3/2009)

Dave Ballantyne (11/3/2009)
select
@TIME1 + ' & a m p; ' + @TIME2
again with the spaces.
Sorry that should be WITHOUT the spaces

Thanks Dave,

I just found the reason why it is converting the '&' to '& a m p;' It is being caused by the blackbox XML method that I'm using to concatenate the row values. I'm trying to use a different method and hoprfully it will work this time. Thanks for all the help ^_^