How to concatenate all columnB where columnA are the same? USING SQL 2005[modified]

Support123

Hi, i have data like this in temp table Owner - DocumentPath 1 - c:\reports\fileA 1 - c:\reports\fileB 1 - c:\reports\fileC 2 - c:\reports\temp\FileA 2 - c:\reports\fileA 2 - c:\reports\fileB AND SO ON.... i would like to know... Is it possible to concatenate all DocumentPath column's into single column for each Owner ? so i would have a new temp table that looks like this (if possible... this is still theoretical) Owner - DocumentPaths 1 - c:\reports\fileAc:\reports\fileBc:\reports\fileC 2 - c:\reports\temp\FileAc:\reports\fileAc:\reports\fileB AND SO ON.... The reason i want to do this is because i need all the file paths in one line in one field on a report later... I thought i could PIVOT the data and then concatenate them... OR Is it possible to select only the top 3 (ordered by Modified Date DESCENDING) for each Owner??? like: SELECT TOP 3 Owner, DocumentPath FROM #tempTableDocPath ORDER BY Owner ASC,Modified DESC BUT for each owner show top 3 (if they have three) AND then Is it possible ONCE AGAIN to CONCATENATE the DocumentPath column into one column (in a new table / tempTable)? like this: Owner - DocumentPath 1 - c:\reports\fileAc:\reports\fileBc:\reports\fileC 2 - c:\reports\temp\FileAc:\reports\fileAc:\reports\fileB AND SO ON.... Thank you in advance. I hope i put it all down clearly :confused:

"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison

modified on Wednesday, December 05, 2007 9:19:40 AM

coolestCoder · modified on Wednesday, December 05, 2007 9:19:40 AM

Hi, 1. You can always write a stored procedure, wherein you can use cursor to concatenate the output of some SQL query. This string can then be returned / stored in some temporary table, which will be used later.

Support123 wrote:

Is it possible to select only the top 3 (ordered by Modified Date DESCENDING) for each Owner??? like: SELECT TOP 3 Owner, DocumentPath FROM #tempTableDocPath ORDER BY Owner ASC,Modified DESC BUT for each owner show top 3 (if they have three) AND then Is it possible ONCE AGAIN to CONCATENATE the DocumentPath column into one column (in a new table / tempTable)? like this: Owner - DocumentPath 1 - c:\reports\fileAc:\reports\fileBc:\reports\fileC 2 - c:\reports\temp\FileAc:\reports\fileAc:\reports\fileB AND SO ON....

I didnt got this. Can you please clarify in details, what exactly you want in this case ??

"A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder

coolestCoder

Support123

i want to put all the rows where the owner is the same (example 1... owner 1 is repeated three times because he has 3 documents linked to him... so he would look like this in the db: Owner - DocumentPath 1 - c:\reports\fileA 1 - c:\reports\fileB 1 - c:\reports\fileC) what i want to do is take all the paths linked to this owner, and put them into one column... to look like this. Owner - DocumentPath 1 - c:\reports\fileAc:\reports\fileBc:\reports\fileC So you see, now i have all the values needed in one column. This makes it TONS easier to display on a report. So Column 1 (Owner) would have the Owner id and the Column 2 (DocumentPath) will have all the DocumentPaths found linked to the owner in column 1. I hope this is more clear???

"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison

coolestCoder

Support123 wrote:

I hope this is more clear???

Yes, it is clear now. Can you provide some table structure for a more precise answer ? It would help me allot.

"A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder

coolestCoder