SQL Server Equivalent of Wm_Concat Function

SQL Server equivalent of WM_CONCAT function

You don't have an equivalent function for that, but you can still simulate (make useof CROSS APPLY and FOR XML PATH('')). example,

USERID  ADDRESSLINE1
==========================
1       First Street
1       Second Street
2       32th Street
2       24th Street
2       25th Street

will result

USERID  ADDRESSLIST
============================
1       First Street, Second Street
2       32th Street, 24th Street, 25th Street

Using this query:

SELECT  a.UserID, 
        SUBSTRING(d.Addresses,1, LEN(d.Addresses) - 1) AddressList
FROM
        (
            SELECT DISTINCT UserID
            FROM tableName
        ) a
        CROSS APPLY
        (
            SELECT [AddressLine1] + ', ' 
            FROM tableName AS B 
            WHERE A.UserID = B.UserID 
            FOR XML PATH('')
        ) D (Addresses) 

SQLFiddle Demo

Which is the equivalent command for Oracle wm_concat in SQL Server?

Try like this

Select Stuff(
        (
        Select ', ' + T2.ColVlaue  // Add a comma (,) before each value
        From MyTable As T2
        Where T2.ID= T1.ID
        For Xml Path(''), type // Select it as XML
        ).value('.', 'nvarchar(max)'), 1, 2, '')  // This is done to remove the first character (,) from the result
From MyTable As T1
Group By T1.Id

Or:

DECLARE @Value VARCHAR(8000)  
SELECT @Value = COALESCE(@Names + ', ', '') + ColValue FROM People
SELECT @Value

Concatenate string in Oracle SQL? (wm-concat)

Yes the WM_CONCAT function puts a comma between each value it concatenates.

If there are no commas in your data you could do this:

SELECT replace (wm_concat('<br>• ' || FIELD1 || ' ' || FIELD2 || ' : ' 
                          || FIELD 3 || ' text'),
               ',', null) AS "Team"

If you are on 11G you can use the new LISTAGG function instead:

SELECT LISTAGG ('<br>• ' || FIELD1 || ' ' || FIELD2 || ' : '
                || FIELD 3 || ' text')
         WITHIN GROUP (ORDER BY <something>) AS "Team"

That will produce a result without commas.

Comma separated column without XML path

Since you are on Oracle 10g version, you cannot use LISTAGG. It was introduced in 11g.

And please DON'T use WM_CONCAT as it is an undocumented feature, and has been removed from the latest release. See Why does the wm_concat not work here?

For 10g, you have following string aggregation techniques:

1. ROW_NUMBER() and SYS_CONNECT_BY_PATH
2. User-defined function STRAGG as demonstrated by Tom Kyte here https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2196162600402
3. COLLECT function in PL/SQL

Following is a pure SQL method using ROW_NUMBER() and SYS_CONNECT_BY_PATH functions available since 9i:

SQL> column emp_list format a50
SQL> SELECT deptno,
  2         LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
  3         KEEP (DENSE_RANK LAST ORDER BY cur),',') AS emp_list
  4  FROM   (SELECT deptno,
  5                 ename,
  6                 ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS cur,
  7                 ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
  8          FROM   emp)
  9  GROUP BY deptno
 10  CONNECT BY prev = PRIOR cur AND deptno = PRIOR deptno
 11  START WITH cur = 1;

    DEPTNO EMP_LIST
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

SQL>

ORACLE/SQL: wm_concat & order by

You can't reference ODB.TASK_CARD_CONTROL.CONTROL_CATEGORY from outside the inner query. Try:

SELECT TASK_CARD, WM_CONCAT(code) as ZONES
FROM (SELECT TASK_CARD, CODE, CONTROL_CATEGORY FROM ODB.TASK_CARD_CONTROL 
      WHERE ODB.TASK_CARD_CONTROL.CONTROL_CATEGORY = 'ZONE'
      ORDER BY CODE)
GROUP BY TASK_CARD

How to concatenate text from multiple rows into a single text string in SQL Server

If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.

I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily.

If there is a table called STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Result I expected was:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

I used the following T-SQL:

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH (''), TYPE
            ).value('text()[1]','nvarchar(max)') [Students]
        FROM dbo.Students ST2
    ) [Main]

You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don't need to do a sub-query:

SELECT DISTINCT ST2.SubjectID, 
    SUBSTRING(
        (
            SELECT ','+ST1.StudentName  AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)'), 2, 1000) [Students]
FROM dbo.Students ST2

---

Related Topics