Solved

Equivalent for wm_concat in SQL Server

Posted on 2011-09-19
5
3,018 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

690 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