CODEWITHSUNDEEP
sqlsql-serversql-server-2008selectsql-server-group-concat
user3239820
user3239820

Reputation:

TSQL: How to Join / Combine rows of a single column into a CSV list

I have a table called "colamer" as shown below:

ID    Title   SectionID
1      abc     231
2      abc     232
3      pqr     95
4      jkl     165

My Stored Procedure:

CREATE PROCEDURE CheckDuplicateBannerforSection  
/*
      colamer_CheckDuplicateBannerforSection 'abc','231,232,233',NULL
*/

@BannerTitle NVARCHAR(200),        
@SectionId VARCHAR(50),        
@Result VARCHAR(100) OUT        
AS        
BEGIN        
 SET @Result = 1        
   BEGIN  
        IF EXISTS        
    (SELECT 1 FROM colamer WHERE Title= @BannerTitle   
 And SectionID in(SELECT value FROM dbo.colamer_fn_Split1( @SectionId ,',')))   
   END  
SELECT @Result

END

I want the OutPut: @result = 231,232

Upvotes: 2

Views: 573

Answers (3)

VahidNaderi
VahidNaderi

Reputation: 2488

I guess you're looking for something like this:

CREATE PROCEDURE CheckDuplicateBannerforSection  

@BannerTitle NVARCHAR(200),        
@SectionId VARCHAR(50),        
@Result VARCHAR(100) OUT        
AS        
BEGIN        
  SET @Result = ''          
  Select @Result = @Result + ',' + Convert(nvarchar, SectionID)
  from colamer where Title = @BannerTitle and SectionID in(SELECT value FROM dbo.colamer_fn_Split1( @SectionId ,','))  
END

Upvotes: 1

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

CREATE PROCEDURE CheckDuplicateBannerforSection  
/*
      Mercola_CheckDuplicateBannerforSection 'abc','231,232,233',NULL
*/

@BannerTitle NVARCHAR(200),        
@SectionId VARCHAR(50),        
@Result VARCHAR(100) OUT        
AS        
BEGIN  
   SELECT @Result = STUFF((SELECT ',' + CONVERT(VARCHAR(50), c.SectionID)
                           FROM colamer c
                           CROSS APPLY dbo.colamer_fn_Split1(@SectionId, ',') AS A 
                           WHERE c.SectionID = A.value AND Title = @BannerTitle   
                           FOR XML PATH('')
                          ), 1, 1, ''
                         )            
END

Upvotes: 1

Dgan
Dgan

Reputation: 10295

CREATE PROCEDURE Mercola_CheckDuplicateBannerforSection  
/*
      Mercola_CheckDuplicateBannerforSection 'abc','231,232,233',NULL
*/

@BannerTitle NVARCHAR(200),        
@SectionId VARCHAR(50),        
@Result VARCHAR(100) OUT        
AS        
BEGIN        
 SET @Result = 1        
   BEGIN  
 IF EXISTS (SELECT 1 FROM colamer
             WHERE Title = @BannerTitle   
            And SectionID 
           in(SELECT value FROM 
             dbo.colamer_fn_Split1( @SectionId ,',')))   
BEGIN
    SELECT top 1 @Result=SectionID FROM colamer
    WHERE Title = @BannerTitle   
    And SectionID in(SELECT value FROM 
    dbo.colamer_fn_Split1( @SectionId ,','))
END
   END  
SELECT @Result  

END

Upvotes: 0

Related Questions