Help with SQL query

Question

User521323632 posted

 I have the following sql query:

 

SELECT DISTINCT work_tickets.ticket_id, work_tickets_assignment.assigned_to
FROM            work_tickets INNER JOIN
                         work_tickets_assignment ON work_tickets.ticket_id = work_tickets_assignment.ticket_id
WHERE        (work_tickets_assignment.assigned_to IN
                             (SELECT        assigned_to
                               FROM            work_tickets_assignment AS work_tickets_assignment_1
                               WHERE        (ticket_id = work_tickets.ticket_id)))
GROUP BY work_tickets.ticket_id, work_tickets_assignment.assigned_to

 

 

What I need it to return is:

ticket_id                             assigned_to

1                                       bsmith csmith dsmith esmith  etc.....

 

What it's returning is:

ticket_id                        assigned_to

1                                   bsmith

1                                   csmith

1                                   dsmith

 

What am I doing wrong? 

 

Thanks for your help!

 

 

Edit: Forgot to mention, each row in work_tickets_assignment is like this:

ticket_id       assigned_to

1                      username

 

If a ticket is assigned to multiple people, the ticket_id will be the same and a new row will be inserted for each person it is assigned to

Answer 1

User600176218 posted

Check out the coalesce statement.  There's a great example at http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string, under the "New and Improved Approach" section.  They are creating a CSV, but you can replace the one comma with a space to do what you want.

Answer 2

User521323632 posted

 I'm sorry but I am having a hard time seeing what you are saying will work for me. Could you be a little more specific?

 

Thanks!

Answer 3

User600176218 posted

What you want to do is a little tricky, and I had a few minutes, so I worked this example out.  I had to guess at your table structure, so you'll have to tweak the sql in a couple places.  Since you want a list associated with an ID, you need to loop through a list of tickets and build the user names one at a time.  That means a few intermediate SQL things going on here, but this will get you what you need.  Not extremely scalable, but will get the job done for small to medium tables.

1    -- table variable to hold intermediate results
2    declare @assignments table(ticket_id int, assigned_to varchar(100))
3   
4    --couple of variables used in the loop
5    declare @assigned_to varchar(100),
6    @ticket_id int
7   
8    -- populate the table var with a list of all tickets
9    -- modify sql to suit your needs
10   insert into @assignments(ticket_id)
11   select distinct ticket_id from work_tickets
12   order by ticket_id
13  
14   -- get the first ticket w/o any assignment information
15   select top 1 @ticket_id = ticket_id
16   from @assignments
17   where assigned_to is null
18  
19   -- if anything was retrieved in the previous query
20   -- when we reach the end of the table, @@rowcount=0 and loop will end
21   while @@rowcount >0
22   begin
23  
24   -- get all users assigned to the work ticket and join them into a space
25   -- delimited list
26   -- modify join and where to suit your schema
27   select @assigned_to = coalesce(@assigned_to + ' ','') + convert(varchar(6),user_name)
28   from work_tickets_assignment as a
29   inner join work_tickets as t on a.user_id = t.assigned_to_id
30   where t.ticket_id = @ticket_id
31  
32   -- add the user list into the table var
33   update @assignments
34   set assigned_to = @assigned_to
35   where ticket_id = @ticket_id
36  
37   -- important!! clear the list
38   set @assigned_to = ''
39  
40   -- get the next ticket w/o assignment info
41   -- loop will end if we're done
42   -- this query should match the one outside the loop
43   select top 1 @ticket_id = ticket_id
44   from @assignments
45   where assigned_to is null
46  
47   end
48  
49   -- return the contents of the table var
50   select * from @assignments
51  

 My sample tables 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

I'm going to put this example on my blog, it's kind of a good puzzle.

Answer 4

User1281381861 posted

Hi,arjones

That CAn Be done thrugh Stuff,For Xml PAth ,Cross Apply

It is the matter of concatenation the rows

 Try below Two Select Quereies They will help u

 

 

GO

 

Create table Test_Concat

(id int ,assigned_to varchar(100))

Go

insert into Test_Concat

select 1 , 'bsmith'

Union all

select 1 , 'csmith'

Union all

select 1 , 'dsmith'

union all

select 2 , 'esmith'

Union all

select 2 , 'fsmith'

Union all

select 3 , 'gsmith'

Go

GO

Select distinct id,

(

stuff(

(

Select ' ' + assigned_to

from Test_Concat emp

where emp.id=empnew.id

order by id

For Xml Path ('')),1,1,'')) as assigned_to

from Test_Concat empnew

GO

--Output

id assigned_to

1 bsmith csmith dsmith

2 esmith fsmith

3 gsmith

 

 

 

GO

Select distinct id,STUFF(M.m_list, 1, 1, '') as assigned_to FROM Test_Concat empnew

cross apply

(

Select ' ' + assigned_to

from Test_Concat emp

where emp.id=empnew.id

order by id

For Xml Path ('')) as M (m_list)

GO

--Output

id assigned_to

1 bsmith csmith dsmith

2 esmith fsmith

3 gsmith

 

Further U can Get Help from this

 

 

http://forums.asp.net/t/1348153.aspx
http://forums.asp.net/p/1362011/2815779.aspx
http://www.projectdmx.com/tsql/rowconcatenate.aspx