Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4841
  • Last Modified:

wm_concat for sqlserver

What is the equivalent of the oracle wm_concat function for SqlServer 2005+?
0
rmtdev2
Asked:
rmtdev2
1 Solution
 
vinurajrCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
rmtdev2Author Commented:
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
 
Anthony PerkinsCommented:
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
 
Anthony PerkinsCommented:
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
 
rmtdev2Author Commented:
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
 
Anthony PerkinsCommented:
>>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
 
rmtdev2Author Commented:
I found that this was the cleanest way
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now