Concatenation of Records in SQL Server

Question

I need to fetch the data from SQL Server based on the below condition.

In the database there are columns Ticketnumber and comments. For a Ticketnumber there are multiple records, for the same ticket number there are multiple comments stored in different records. Whenever I retrieve the ticketnumber and comments columns, the comments should be concatenated in the single record.

Let's look at an example:

Ticketnumber       Comments        
-------------------------------
1002               Case raised    
1003               Case raised
1002               Processing,
1003               resolved
1002               resolved

Expected output should be:

Ticketnumber       Comments
-----------------------------------------------
1002               Case raise,processing,resolved,
1003               case raised,resolved

All the comments should be concatenated for single ticketnumber as above.

Please provide the query. Thanks in advance

score 0 · Answer 1 · edited May 23 '17 at 12:30

0

There was a previous post w/ an answer for something similar here:

Concatenate many rows into a single text string?

edited May 23 '17 at 12:30

Community

1
1

answered Mar 31 '15 at 19:53

Graham

336
1
13

you should post the answer here as well (along with the link with the source), since the link may become unavailable. – cr0ss Mar 31 '15 at 19:56
It's a stackoverflow link. If anything, he should flag the question as a duplicate. – Tab Alleman Mar 31 '15 at 20:07

score 0 · Accepted Answer · edited May 23 '17 at 12:30

0

Try this:

Select X.Ticketnumber,
       Left(X.yourtable,Len(X.yourtable)-1) As "comments"
From
    (
        Select distinct y.Ticketnumber, 
            (
                Select x.comments  + ',' AS [text()]
                From dbo.yourtable x
                Where x.Ticketnumber= y.Ticketnumber
                ORDER BY x.SubjectID
                For XML PATH ('')
            ) [yourtable]
        From dbo.yourtable y
    ) [X]

Taken help from the source thread

edited May 23 '17 at 12:30

Community

1
1

answered Mar 31 '15 at 19:57

Rahul Tripathi

168,305
31
280
331

Thanks Rahul, for your quick response, If i need to pull multiple columns apart from this then where should i add column names. – shaik ahamad Mar 31 '15 at 20:04
@shaikahamad:- `If you want to pull multiple columns`..I didnt get that! – Rahul Tripathi Mar 31 '15 at 20:06
Above we are fetching two columns only right, if any need to fetch other columns too, then how? ex: Ticketnumber , comments, id , user, time these are the columns i need to fetch, but Comments only i need to concatenate . – shaik ahamad Mar 31 '15 at 20:11
@shaikahamad:- You just have to add it at the first select like this: `Select X.Ticketnumber, Left(X.yourtable,Len(X.yourtable)-1) As 'comments',id,user,time....` – Rahul Tripathi Mar 31 '15 at 20:13
@shaikahamad:- Hope this answers your question! – Rahul Tripathi Mar 31 '15 at 20:13
Thank you , Let me try this. i believe it would work – shaik ahamad Mar 31 '15 at 20:18
@shaikahamad:- Do accept the answer if that helps. (The right tick mark turning green when clicked) – Rahul Tripathi Mar 31 '15 at 20:23
I got this error : [SQL Server]The data types text and varchar are incompatible in the add operator. – shaik ahamad Apr 01 '15 at 17:42
Hi , i have tried this code it is giving little diff results, means comments are not coming in order, it is coming in the order of comment 3 ,comment 1 and comment 2 – shaik ahamad Aug 10 '15 at 17:54

Concatenation of Records in SQL Server

2 Answers2