Solved

wm_concat for sqlserver

Posted on 2011-02-23
9
4,493 Views
Last Modified: 2012-05-11
What is the equivalent of the oracle wm_concat function for SqlServer 2005+?
0
Comment
Question by:rmtdev2
[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
9 Comments
 
LVL 8

Expert Comment

by:vinurajr
ID: 34959146
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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 

Author Comment

by:rmtdev2
ID: 34966610
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34967035
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
ID: 34967040
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
ID: 34967618
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
ID: 34976284
>>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
ID: 34995598
I found that this was the cleanest way
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

729 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