Wrapping same column with same id in sql server

Question

I want to wrap the column which have two different messages for same queueID and it should not be hard-coded on messages column.

This is my table :

CREATE TABLE [dbo].[test]   
(
   [id] [int] IDENTITY(1,1) NOT NULL,
   [queueID] [int] NULL,
   [messages] [nvarchar](50) NULL,
   [firstname] [nvarchar](20) NULL,
   [secondname] [nvarchar](20) NULL
)

Table Input are :

insert into test
values (1,'Connection failed','j','s')
, (1,'Connection failed','j','s')
, (1,'Connection failed','j','s')
, (2,'Connection failed','j','s')
, (2,'Error message','j','s')
, (2,'Connection failed','j','s')
, (3,'Connection failed','j','s')
, (3,'Connection failed','j','s')
, (4,'Connection failed','j','s')
, (4,'Error message','j','s')
, (4,'third party','j','s')
, (5,'Error message','j','s')
, (5,'third party','j','s')

In Above table my expected result is

   queueID       messages
------------------------------------------------
     1          Connection failed
     2          Connection failed,Error message
     3          Connection failed
     4          Connection failed,Error message,third party
     5          Error message,third party

Possible duplicate of [Concatenate many rows into a single text string?](http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string) — mustaccio, Jan 04 '17 at 19:55
Firstly, you need something to order records within each `queueID`. How do you know that "Error message" comes _after_ "Connection failed" and not before? — mustaccio, Jan 04 '17 at 19:57

score 0 · Answer 1 · answered Jan 04 '17 at 20:05

0

Try this

 select
    queueID,

    STUFF((select ','+ messagess From test as t2 where t2.queueID = t1.queueID FOR XML PATH('')),1,1,'')

    FROM test as t1
    GROUP BY queueID,messages

answered Jan 04 '17 at 20:05

LONG

4,490
2
17
35

It should not be concatenated with same messagess and also distinct queueID – Jagath Jan 04 '17 at 20:42
Thank you for trying it LONG – Jagath Jan 04 '17 at 20:58

abraxascarab · Answer 2 · 2017-01-04T20:56:43.777

If this is SQL Server, you can not do a group_concat().

Instead you can use XML functionality to do the same:

SELECT 
   id.[queueID]
   ,[messagess] = STUFF(
    (SELECT ', ' + mess.[messagess]
     FROM (SELECT [queueID], [messagess] 
           FROM test
           GROUP BY [queueID], [messagess]) as mess  -- This gets rid of duplicate messages within each queueID.
     WHERE id.[queueID] = mess.[queueID]
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM 
   (SELECT [queueID] 
    FROM test
    GROUP BY [queueID]) as id -- This gets your unique queueID's
ORDER BY 
   id.[queueID];

Wrapping same column with same id in sql server

2 Answers2