Concatenate Strings in MS SQl server using Row Number

Posted on 2011-04-22
Last Modified: 2012-05-11

I have a query returning results like

TicketNo              Operator                  Operator_Group
Ticket1                 OperatorA               AGroup
Ticket1                 OperatorB               BGroup
Ticket2                 OperatorA              AGroup
Ticket2                OperatorB               BGroup
Ticket2                OperatorC                CGroup

I'm trying to concatenate the Operator field and Operator_Group field for each ticket no. for example

Ticket1               Operator A, OperatorB                                 AGroup, Bgroup
Ticket2               Operator A, OperatorB, OperatorC                AGroup, Bgroup, CGroup

How can I do this? please assist. Thank you

Question by:angel7170
    LVL 9

    Expert Comment

    Can't you use groupby ticketno? You did you do this kind of concatenations here in SQL.

    If you have any front end application for this, USe the GROUPBY in sql query and perform this concat operation in front end.
    LVL 22

    Expert Comment

    by:Nico Bontenbal
    See question:
    Comment 35439992 has an example. All you need to do is create a second function (or add a parameter to the function) for your second column.
    LVL 21

    Expert Comment

    by:Alpesh Patel
    You can achieve by SP or Function with TempTable other than single Query.
    LVL 69

    Assisted Solution

    by:Éric Moreau
    LVL 6

    Accepted Solution

    Something like this should work:

    Replace table_name where applies.
    select ticket_no, o.*, og.*
     from table_name t
     cross apply (select o = stuff((select ', ' + operator from table_name o where t.ticketno = o.ticketno for xml path('')),1,2,'')) o
     cross apply (select og = stuff((select ', ' + operator_group from table_name og where t.ticketno = og.ticketno for xml path('')),1,2,'')) og

    Open in new window

    LVL 6

    Expert Comment

    I think you'll want "select distinct  ticket_no, o.*, og.*" for that first line.
    LVL 69

    Expert Comment

    by:Éric Moreau

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Round up Or down 17 36
    SQL Question 1 26
    SQL Server; storing data in offline mode. 10 47
    MS SQL 2005 Srink database in chunks 4 33
    There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
    In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    This video discusses moving either the default database or any database to a new volume.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now