Concatenation Isssue (SELECT QUERY)

here is the sample data. I want a query which can fetch me a single record that can concatenate the value in the 3rd column for the same value in 1st column. do let me know if any understanding issue is there.

XZZZZZQPD2X2NF0WIYPHUFQHB5OLU515 2 arrier and DeltaV Controller is
XZZZZZQPD2X2NF0WIYPHUFQHB5OLU515 3 nets and field equipment interface.
XZZZZZQPD2X2NF0WIYPHUFQHB5OLU515 1 This is the quote for RS3 migration

Thanks,
Rahul Jhasomething like SUM() for varchar data type.|||avast, you should be doing this in your application layer, me hearty

:)|||have just gt a code. wanted to share this with others, and looking forward for the comment from the forum.

-- Prepare sample data
DECLARE @.Sample TABLE (ID INT, Code VARCHAR(3))

INSERT @.Sample
SELECT 290780, 'LT' UNION ALL
SELECT 290780, 'AY' UNION ALL
SELECT 290781, 'ILS' UNION ALL
SELECT 290780, 'AY'

SELECT * FROM @.Sample

-- Show the expected output
SELECT DISTINCT s1.ID,
STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + s2.CODE FROM @.Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM @.Sample AS s1
ORDER BY s1.ID

SELECT DISTINCT s1.ID,
STUFF((SELECT TOP 100 PERCENT ',' + s2.CODE FROM @.Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM @.Sample AS s1
ORDER BY s1.ID

SELECT DISTINCT s1.ID,
STUFF((SELECT ',' + s2.CODE FROM @.Sample AS s2 WHERE s2.ID = s1.ID FOR XML PATH('')), 1, 1, '') AS CODES
FROM @.Sample AS s1
ORDER BY s1.ID|||but my database is 2000. not 2005. and in 2000 the above code will not work.|||Why not do it in the presentation layer/|||This here DBA needs to walk the plank.|||Arggggghhhh

shiver me timbers|||Your wood is cold..?
I just don't even want to know :p

...seriously, what doesthat phrase even mean?|||...seriously, what doesthat phrase even mean?haaaarr, ye be a pitiful excuse for a young pirate, me lad

http://en.wikipedia.org/wiki/Shiver_my_timbers|||hahaha its true its Pirate day today :) aaaaaarrrrrrr|||Arggggghhhh

shiver me timbers

Brett, Opie and Anthony fan ?

They had pirate talk today.|||buffett

He mentioned it last night at his concert at MSG|||buffett

He mentioned it last night at his concert at MSG

Rush Fan ? they played Monday night at MSG.|||Rush Fan ? they played Monday night at MSG.

In another lifetime|||Original Quote Posted By MCrowley.....
Why not do it in the presentation layer/

No I can't do this at the presentation layer. This issue has come duriong the data migration phase. It has to be done at the DB side only.........

Thanks,
Rahul Jha|||Why does it have to be during data migration?
Chances are that you are de-normalising your data by concatenation.|||Why does it have to be during data migration?
Chances are that you are de-normalising your data by concatenation.

Actaully m normalising the DB. currently it's in De-normalised state......

Thanks,
Rahul Jha|||Ha!

Anyhow, have you done any googling? http://www.google.co.uk/search?hl=en&q=how+to+concatenate+in+SQL+msdn2&meta=|||Chances are that you are de-normalising your data by concatenation.that's a bit tentative, isn't it george?

for sure it's denormalizing the data

:)|||I can think of one single example where concatenation (ok, it's not really concatenation, but you can't blame a guy for trying!) would not cause denormalisation.

DateField + TimeField

Bleugh.|||You can try this out...

"SELECT THIRD_COLUMN+FIRST_COLUMN FROM TABLE"

...it works if both are varchar type columns.
if you wanna add some special character inbetween ...

THIRD_COLUMN+'-'+FIRST_COLUMN will solve your purpose.|||I can think of one single example where concatenation (ok, it's not really concatenation, but you can't blame a guy for trying!) would not cause denormalisation.

DateField + TimeField

Bleugh.actually, that is "really concatenation" :)

what he wants to do is aggregation (a column operation over several rows)

it's a reasonable request

in MySQL, the GROUP_CONCAT aggregate function performs exactly this operation, with options for the separator and sequence of terms|||Ha!

Anyhow, have you done any googling? http://www.google.co.uk/search?hl=en&q=how+to+concatenate+in+SQL+msdn2&meta=

How is it gonna help Georgy by any chance...........?? Have a look again on the query............. And if the link can help me to qrite the query then kindly guide me thru.........

Thanks,
Rahul Jha|||what he wants to do is aggregation (a column operation over several rows)

ur rgt.......

Thanks,
Rahul Jha|||start here:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html|||ur rgt.......

Thanks,
Rahul Jha

I love those phone commercials

omg wtf lol roflmao|||buffett

He mentioned it last night at his concert at MSG

You went all the way from the Channel Islands to Madison Square Gardens?! Wow, you are a serious Jimmy Buffett fan!! :D|||ur rgt.......OMG, is there a new instance of The Great Bangalore Alphabet Famine of 1978 (www.nevermind-Im-just-kidding) going on?

Horrors.

Fortunately, relief packages of 1000 letters are available on a first-come-first-served basis at the CIIL (http://www.ciil.org/)|||he is debugging his app on a cell phone while driving

using only his thumb

actually, i will cut him a lot of slack because of this

:cool: