How to create a CSV or delimited list with an ID field in t-sql

A question in the asp.net forums wanted to return the following output:


ticket_id          assigned_to
1                       bsmith csmith dsmith


but was getting


ticket_id     assigned_to
1                  bsmith
1                  csmith
1                  dsmith


Since T-SQL (an query languages in general) lacks a lot of the array and concatenation features we take for granted in programming languages, you need to do a little trickery to make this work.  There are probably several ways to pull this off, but here’s what I did.


My first thought was the great creating a CSV list in SQL using coalesce example.  But, the tricky part here is that he wanted the ID of the ticket along with the list of the users assigned to the ticket, and you can’t assign a value and retrieve data in the same statement.  So that means we have to use some sort of updateable array (which is usually a table variable or temp table in T-SQL) and a loop (I’m not partial to cursors, so I used a WHILE loop).


The original question had two tables joined together, so I tried to mimic the structure for my solution.  The tables I used looked like this:



work_tickets
ticket_id     assigned_to_id
1                 1
1                 2
1                 3



work_tickets_assignment
user_id     user_name
1              bsmith
2              csmith
3              dsmith


And here’s the SQL I ended up with.



— table variable to hold intermediate results
declare @assignments table(ticket_id int, assigned_to varchar(100))


–couple of variables used in the loop
declare @assigned_to varchar(100),
    @ticket_id int


— populate the table var with a list of all tickets
— modify sql to suit your needs
insert into @assignments(ticket_id)
select distinct ticket_id from work_tickets
order by ticket_id


— get the first ticket w/o any assignment information
select top 1 @ticket_id = ticket_id
from @assignments
where assigned_to is null


— if anything was retrieved in the previous query
— when we reach the end of the table, @@rowcount=0 and loop will end
while @@rowcount >0
begin


    — get all users assigned to the work ticket and join them into a space
    — delimited list
    — modify join and where to suit your schema
    select @assigned_to = coalesce(@assigned_to + ‘ ‘,”) + convert(varchar(6),user_name)
    from work_tickets_assignment as a
    inner join work_tickets as t on a.user_id = t.assigned_to_id
    where t.ticket_id = @ticket_id


    — add the user list into the table var
    update @assignments
    set assigned_to = @assigned_to
    where ticket_id = @ticket_id


    — important!! clear the list
    set @assigned_to = ”


    — get the next ticket w/o assignment info
    — loop will end if we’re done
    — this query should match the one outside the loop
    select top 1 @ticket_id = ticket_id
    from @assignments
    where assigned_to is null


end


— return the contents of the table var
select * from @assignments









This have me the exact result the questioner wanted.  It’s not the most scalable piece of code, but in a test on some other data I have, I was able to update over 800+ unique “tickets” with over 2000 “users” in under 4 seconds.  Like I said, not the most scalable, but it will get the job done for smaller data sets.