none
Concatenating data

    Question

  • my tsql query

    select  distinct c.companyName+'~'+CAST(c.CompanyID as varchar) as [Company Name],    
       r.RoleName as [Role]    
     from dbo.tblDrugDeliveryTechnology t    
    left join dbo.tblTP tp on tp.TID=t.TID    
    left join dbo.tblcompany c on c.CompanyID=tp.CompanyID    
    left join dbo.tblPartnerRole pr on pr.TPID=tp.TPID    
    left join dbo.tblRole r on  r.RoleID=pr.RoleID    
    where t.TID in (13437, 13438)

    outputs:

    Company Name    Role
    Halozyme Therapeutics Inc~7898 Current Owner
    Halozyme Therapeutics Inc~7898 Developer
    Halozyme Therapeutics Inc~7898 Originator
    Intezyne Inc~9173 Current Owner
    Intezyne Inc~9173 Developer
     

    and i would like it to do:

    Company Name   Role

    Halozyme Therapeutics Inc~7898 Current Owner, Developer, Originator

    Intezyne Inc~9173 Current Owner, Developer


    • Edited by Ajai_N Thursday, February 14, 2013 3:14 PM
    Thursday, February 14, 2013 3:13 PM

Answers

  • Try:

    select  c.companyName+'~'+CAST(c.CompanyID as varchar(10)) as [Company Name],   stuff((select ', ' +  
       r.RoleName    
    from dbo.tblPartnerRole pr 
    INNER join dbo.tblRole r on  r.RoleID=pr.RoleID    
    where pr.TPID=tp.TPID ORDER BY r.RoleName   
    FOR XML PATH('')),1,2,'') as [Role]
    
     from dbo.tblDrugDeliveryTechnology t    
    left join dbo.tblTP tp on tp.TID=t.TID    
    left join dbo.tblcompany c on c.CompanyID=tp.CompanyID GROUP BY c.companyName, c.CompanyID   


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    Thursday, February 14, 2013 3:19 PM
  • ;with mycte as
    (
    select  distinct c.companyName+'~'+CAST(c.CompanyID as varchar) as [CompanyName],    
       r.RoleName as [Role]    
     from dbo.tblDrugDeliveryTechnology t    
    left join dbo.tblTP tp on tp.TID=t.TID    
    left join dbo.tblcompany c on c.CompanyID=tp.CompanyID    
    left join dbo.tblPartnerRole pr on pr.TPID=tp.TPID    
    left join dbo.tblRole r on  r.RoleID=pr.RoleID    
    where t.TID in (13437, 13438)
    )
    
    SELECT   [CompanyName], 
    (STUFF((SELECT ',' + CAST(Role AS varchar(50)) FROM  mycte WHERE m.CompanyName=CompanyName
     FOR XML PATH('')), 1, 1, '')) AS Roles
    FROM  mycte AS m
    GROUP BY [CompanyName]
    Order by [CompanyName]
    

    • Marked as answer by Ajai_N Thursday, February 14, 2013 3:43 PM
    Thursday, February 14, 2013 3:24 PM

All replies

  • Try:

    select  c.companyName+'~'+CAST(c.CompanyID as varchar(10)) as [Company Name],   stuff((select ', ' +  
       r.RoleName    
    from dbo.tblPartnerRole pr 
    INNER join dbo.tblRole r on  r.RoleID=pr.RoleID    
    where pr.TPID=tp.TPID ORDER BY r.RoleName   
    FOR XML PATH('')),1,2,'') as [Role]
    
     from dbo.tblDrugDeliveryTechnology t    
    left join dbo.tblTP tp on tp.TID=t.TID    
    left join dbo.tblcompany c on c.CompanyID=tp.CompanyID GROUP BY c.companyName, c.CompanyID   


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    Thursday, February 14, 2013 3:19 PM
  • ;with mycte as
    (
    select  distinct c.companyName+'~'+CAST(c.CompanyID as varchar) as [CompanyName],    
       r.RoleName as [Role]    
     from dbo.tblDrugDeliveryTechnology t    
    left join dbo.tblTP tp on tp.TID=t.TID    
    left join dbo.tblcompany c on c.CompanyID=tp.CompanyID    
    left join dbo.tblPartnerRole pr on pr.TPID=tp.TPID    
    left join dbo.tblRole r on  r.RoleID=pr.RoleID    
    where t.TID in (13437, 13438)
    )
    
    SELECT   [CompanyName], 
    (STUFF((SELECT ',' + CAST(Role AS varchar(50)) FROM  mycte WHERE m.CompanyName=CompanyName
     FOR XML PATH('')), 1, 1, '')) AS Roles
    FROM  mycte AS m
    GROUP BY [CompanyName]
    Order by [CompanyName]
    

    • Marked as answer by Ajai_N Thursday, February 14, 2013 3:43 PM
    Thursday, February 14, 2013 3:24 PM
  • Yes this works thanks

    • Edited by Ajai_N Thursday, February 14, 2013 3:43 PM
    Thursday, February 14, 2013 3:39 PM
  • Change all your JOINs to INNER JOIN instead of LEFT JOIN. Also, have you tried my query?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, February 14, 2013 3:41 PM
  • Why not use ISNULL(r.RoleName,'') incase of any null value.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, February 14, 2013 3:44 PM