How to make a SQL query adding in a field with multiple entries

Question

HELP

My Query:

SELECT          
      [_ResourceGuid]
      ,[TICKET]
            = STUFF((
                SELECT ',' + [Ticket Number]
                FROM [Inv_Service_Desk_Ticket]
                Where _ResourceGuid = _ResourceGuid
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')
                FROM Inv_Service_Desk_Ticket]

Results Should look like:

[_Resource Guid]        TICKET
klsdro72934579072       234234,76456,56756
lkjd1234907812302       456456,34345,45455


Table Fields: 
ID   ResourceTicket     Number
23   lkjd1234907812302  456456
123  lkjd1234907812302  34345  
345  lkjd1234907812302  45455  
233  klsdro72934579072  234234  
567  klsdro72934579072  76456  
978  klsdro72934579072  56756

Notice, the "ID" is unique and the Ticket Number is unique, the Resource can be duplicated

justiceorjustus · Accepted Answer · 2017-07-07 14:05:52Z

1

Try this:

SELECT DISTINCT
a.[_ResourceGuid] 
, STUFF((
SELECT ',' + [Ticket Number]
FROM [Inv_Service_Desk_Ticket]
Where _ResourceGuid = a._ResourceGuid
FOR XML PATH('')
, TYPE).value('.', 'varchar(max)'), 1, 2, '')
FROM [Inv_Service_Desk_Ticket] a

Edit: Stuff formatted correctly, I think... didn't test it. Stuff is just a subquery where you put it in an XML. You have to add it to your main query like it's a column.

edited Jul 7, 2017 at 14:05

answered Jun 28, 2017 at 21:01

justiceorjustus

1,9752 gold badges21 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to make a SQL query adding in a field with multiple entries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related