Solved

wm_concat for sqlserver

Posted on 2011-02-23
9
4,231 Views
Last Modified: 2012-05-11
What is the equivalent of the oracle wm_concat function for SqlServer 2005+?
0
Comment
Question by:rmtdev2
9 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
Comment Utility
0
 
LVL 10

Expert Comment

by:Asim Nazir
Comment Utility
0
 
LVL 8

Expert Comment

by:vinurajr
Comment Utility
Is wm_concat does a comma separate delimited string..? this can be done in sql server using COALESCE

just give a search for COALESCE you can find the examples.
0
 

Author Comment

by:rmtdev2
Comment Utility
SELECT
   SUBSTRING(buzz, 2, 2000000000)
FROM
    (
    SELECT 
        rsp_id
    FROM 
        caproductrsphrase
    WHERE
        i_id = 1
    FOR XML PATH (',')
    ) fizz(buzz)

Open in new window


I tried the code above and got and error: Row name ',' contains an invalid XML identifier as required by FOR XML; ','(0x002C) is the first character at fault.

When I try without the comma my result is embedded with xml tags:
rsp_id>48</rsp_id><rsp_id>78</rsp_id><rsp_id>144</rsp_id><rsp_id>151</rsp_id><rsp_id>181</rsp_id><rsp_id>202</rsp_id>

Could you tell me where I went wrong?

Thanks
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
I suspect this is what you mean:

I will leave it up to you to decide whether you want the trailing comma and how to lose it if you do not want it.
SELECT
   buzz
FROM
    (
    SELECT 
        rsp_id + ', ' + Data()
    FROM 
        caproductrsphrase
    WHERE
        i_id = 1
    FOR XML PATH (',')
    ) fizz(buzz)

If rsp_id is numeric, instead of:
rsp_id + ', ' + Data()
Use:
CAST(rsp_id As varchar(20)) + ', ' + Data()

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Oops there is a typo it should be:
SELECT
   buzz
FROM
    (
    SELECT 
        rsp_id + ', ' + Data()
    FROM 
        caproductrsphrase
    WHERE
        i_id = 1
    FOR XML PATH ('')
    ) fizz(buzz)

If rsp_id is numeric, instead of:
rsp_id + ', ' + Data()
Use:
CAST(rsp_id As varchar(20)) + ', ' + Data()

Open in new window

0
 

Accepted Solution

by:
rmtdev2 earned 0 total points
Comment Utility
thanks, but I got "Data is not a recognized built in function name."

I accomplish it via the following:

SELECT p1.i_id,
       ( SELECT p3.rsp_code + ','
           FROM caproductrsphrase p2, carsphrase p3
          WHERE p2.i_id = p1.i_id
          and p2.rsp_id = p3.rsp_id
          ORDER BY p3.rsp_code
            FOR XML PATH('') ) AS rsp_codes
      FROM caproductrsphrase p1
      GROUP BY i_id

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>I got "Data is not a recognized built in function name."<<
Odd, as it worked fine for me.  Perhaps you are still using SQL Server 2005.
0
 

Author Closing Comment

by:rmtdev2
Comment Utility
I found that this was the cleanest way
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
query help 18 50
SQl help with selection 14 43
Convert column to int 13 26
Visual Studio Front End "Web Forms" For SQL Server Tables 5 38
In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 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

13 Experts available now in Live!

Get 1:1 Help Now