Concatenate Strings in MS SQl server using Row Number

Hello,

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

angel7170Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
hyphenpipeConnect With a Mentor Commented:
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

0
 
radcaesarCommented:
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.
0
 
Nico BontenbalCommented:
See question:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_26969576.html
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.
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
Alpesh PatelAssistant ConsultantCommented:
You can achieve by SP or Function with TempTable other than single Query.
0
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
0
 
hyphenpipeCommented:
I think you'll want "select distinct  ticket_no, o.*, og.*" for that first line.
0
 
Éric MoreauSenior .Net ConsultantCommented:
0
All Courses

From novice to tech pro — start learning today.