SQL Concatenating Cells of Same Column

Question

I'm trying to create a query capable of concatenating the values in the ProfileItemID column with Microsoft SQL Server Management Studio.

Here's the query. First of all, I'm not sure if it's right because I get this error after running it.

    SELECT RoomID, ',' + ProfileItemID 
    FROM RoomProfile
    WHERE RoomID = 1829 
    FOR XML PATH('')

"Conversion failed when converting the varchar value ',' to data type int."

Attached is an image containing information I have of the table and images of the entire screen.

Hopefully, someone can please help me out. I really don't know what to do since I'm very new to SQL still.

Thank you in advance!

Table:

    |----------------------------------------|
    |                RoomProfile             |
    |---------------------|------------------|
    |         RoomID      |   ProfileItemID  |
    |---------------------|------------------|
    |          1829       |         28       |
    |---------------------|------------------|
    |          1829       |         103      |
    |---------------------|------------------|
    |          1829       |         104      |
    |----------------------------------------|

Output:

    |----------------------------------------|
    |          1829       |   28, 103, 104   |
    |----------------------------------------|

Possible duplicate of [Concatenating a column within the same column in SQL Server](https://stackoverflow.com/questions/16945974/concatenating-a-column-within-the-same-column-in-sql-server) — user2065377, Oct 30 '18 at 18:13

Ilyes · Accepted Answer · 2018-10-30T17:59:36.223

3

You can use STUFF() function and FOR XML clause with PATH mode as

CREATE TABLE T
    ([RoomID] int, [ProfileItemID] int)
;

INSERT INTO T
    ([RoomID], [ProfileItemID])
VALUES
    (1829, 28),
    (1829, 103),
    (1829, 104);

SELECT DISTINCT RoomID,
       STUFF(
              (SELECT ',' + CAST(ProfileItemID AS VARCHAR(10))
               FROM T
               FOR XML PATH('')
              ), 1, 1, ''
       ) Result
FROM T;

Returns:

+--------+------------+
| RoomID |   Result   |
+--------+------------+
|   1829 | 28,103,104 |
+--------+------------+

Demo

edited Oct 30 '18 at 17:59

answered Oct 30 '18 at 17:53

Ilyes

14,640
4
29
55

SELECT RoomID, ProfileItemID = STUFF((SELECT DISTINCT ', ' + cast(ProfileItemID AS VARCHAR(10)) FROM RoomProfile b WHERE b.RoomId = a.RoomiD FOR XML PATH('')), 1, 2, '') FROM RoomProfile a Where RoomId = 1829 GROUP BY RoomID <<<<<<< I take it back. Your answer was the right one to go by. Thank you! – ChunkyFresh Nov 02 '18 at 00:26

score 0 · Answer 2 · answered Oct 30 '18 at 17:50

0

You probably just need to convert the id to a string:

SELECT RoomID, ',' + CONVERT(VARCHAR(MAX), ProfileItemID)
FROM RoomProfile
WHERE RoomID = 1829 
FOR XML PATH('')

answered Oct 30 '18 at 17:50

Gordon Linoff

1,242,037
58
646
786

OP is still missing the `STUFF()` call that will format the results the way his Expected Output is. – digital.aaron Oct 30 '18 at 18:08
Beside to what @digital.aaron say, I don't think `VARCHAR(MAX)` needed here cause _ProfileItemID_ datatype is `INT`. – Ilyes Oct 30 '18 at 18:13

Eray Balkanli · Answer 3 · 2018-10-30T18:58:13.207

0

In addition to Gordon's answer, you can use CAST function as well like below:

SELECT RoomID, ',' + Cast(ProfileItemID as varchar(50))

edited Oct 30 '18 at 18:58

answered Oct 30 '18 at 17:54

Eray Balkanli

7,752
11
48
82

1

I don't think `VARCHAR(MAX)` needed here, cause _ProfileItemID_ datatype is `INT`. – Ilyes Oct 30 '18 at 18:11

SQL Concatenating Cells of Same Column

3 Answers3