Solved

Equivalent for wm_concat in SQL Server

Posted on 2011-09-19
5
2,820 Views
Last Modified: 2012-05-12
Is there an equivalent function in SQL Server for the oracle wm_concat.  I need to make two rows into one but cant seem to find a simple function in SQL that will allow this.
0
Comment
Question by:Rhonda Carroll
  • 3
  • 2
5 Comments
 
LVL 22

Expert Comment

by:pivar
ID: 36560579
Hi,

You could use FOR XML PATH, like

SELECT ID, STUFF((SELECT ', '+t2.name AS[text()] FROM table1 t2 WHERE t2. ID =t1. ID FOR XML PATH('')), 1, 2, '') AS list
FROM table1 t1
GROUP BY t1.ID

/peter
0
 

Author Comment

by:Rhonda Carroll
ID: 36560712
This works for my character field there is a lot of syntax .  Is there a way to do it for a numeric field.  Ideally I would like to just have the string comma delimited if possible
0
 
LVL 22

Accepted Solution

by:
pivar earned 500 total points
ID: 36560788
Then you have to convert the numeric field to a char field.

SELECT ID, STUFF((SELECT ', '+CONVERT(varchar(10), t2.numericvalue) AS[text()] FROM table1 t2 WHERE t2. ID =t1. ID FOR XML PATH('')), 1, 2, '') AS list
FROM table1 t1
GROUP BY t1.ID
0
 

Author Comment

by:Rhonda Carroll
ID: 36561023
That works,  I am almost there.  Is there a way to get rid of the >,6.60000</T><T>,79.20000</T> ... I would like to get the values separated by the comma only .. eliminating the </T><T>,
0
 

Author Comment

by:Rhonda Carroll
ID: 36561132
never mind figured it out, just removed the as piece from the string
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question