locked
Help with SQL query RRS feed

  • 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

    Monday, December 22, 2008 2:47 PM

Answers

  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 26, 2008 1:57 PM
  • 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 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 26, 2008 2:21 PM

All replies

  • 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.

    Monday, December 22, 2008 3:33 PM
  • 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!

    Monday, December 22, 2008 3:38 PM
  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 26, 2008 1:57 PM
  • 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 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 26, 2008 2:21 PM