Prev: Adding data to partitioned table
Next: Noise Word and Thesaurus UDF
From: mttc on 25 Jul 2008 11:08
I find this at:
http://www.sqlservercurry.com/2008/06/combine-multiple-rows-into-one-row.html


==================================
-- Query to combine multiple rows into one
DECLARE @str VARCHAR(100)
SELECT @str = COALESCE(@str + '|', '') + [Numbers]
FROM #Temp
Print @str

You can also achieve the same result using STUFF

SELECT DISTINCT STUFF( (SELECT '*' + Numbers from #Temp FOR XML
PATH('')),1,1,'') as Numbers FROM #Temp
==================================

the first option is work only for one set of details rows, I need to
do that for many rows, so the second option on above simple is good
for me, but i ned it for SQL2000.
Have some other solution?
From: Madhivanan on 28 Jul 2008 06:10
On Jul 25, 8:08 pm, mttc <mtczx...(a)yahoo.com> wrote:
> I find this at:
>  http://www.sqlservercurry.com/2008/06/combine-multiple-rows-into-one-....
>
> ==================================
> -- Query to combine multiple rows into one
> DECLARE @str VARCHAR(100)
> SELECT @str = COALESCE(@str + '|', '') + [Numbers]
> FROM #Temp
> Print @str
>
> You can also achieve the same result using STUFF
>
> SELECT DISTINCT STUFF( (SELECT '*' + Numbers from #Temp FOR XML
> PATH('')),1,1,'') as Numbers FROM #Temp
> ==================================
>
> the first option is work only for one set of details rows, I need to
> do that for many rows, so the second option on above simple is good
> for me, but i ned it for SQL2000.
> Have some other solution?

http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx
 | 
Pages: 1
Prev: Adding data to partitioned table
Next: Noise Word and Thesaurus UDF