Solved

Equivalent for wm_concat in SQL Server

Posted on 2011-09-19
5
2,772 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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

895 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now