Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Concatenating data

Answered Concatenating data

  • Thursday, February 14, 2013 3:13 PM
     
     
    Sign In to Vote
    0

    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
    •  
     

All Replies

  • Thursday, February 14, 2013 3:19 PM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    1

    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:24 PM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    1
     
    ;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:39 PM
     
     
    Sign In to Vote
    0

    Yes this works thanks

    • Edited by Ajai_N Thursday, February 14, 2013 3:43 PM
    •  
     
  • Thursday, February 14, 2013 3:41 PM
    Moderator
     
     
    Sign In to Vote
    0
    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:44 PM
     
     
    Sign In to Vote
    0
    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.