CODEWITHSUNDEEP
sql
CO-VROD
CO-VROD

Reputation: 47

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

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

Upvotes: 0

Views: 44

Answers (1)

justiceorjustus
justiceorjustus

Reputation: 1965

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.

Upvotes: 1

Related Questions