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

Question

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

@Pranav In your example title `abc` have only one SectionID 231 then how can you fetch 231,231 as output. Please clarify... — Saharsh Shah, Jan 02 '15 at 12:53
possible duplicate of [Simulating group\_concat MySQL function in SQL Server?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server) — Pரதீப், Jan 02 '15 at 19:01

Saharsh Shah · Answer 1 · 2015-01-02T13:45:07.663

1

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

edited Jan 02 '15 at 13:45

answered Jan 02 '15 at 13:16

Saharsh Shah

28,687
8
48
83

Why use `CROSS APPLY`? It is not necessary. The original `IN` list was fine, or an `INNER JOIN` would work. – Solomon Rutzky Jan 02 '15 at 13:26
Error: Conversion failed when converting the varchar value ',' to data type int. – Jan 02 '15 at 13:43
This'll work because the characters will always be only numbers, but if you use this `xml path('')` method for textual data, it could mess up characters that need xml-entities like `&`. Solve this by doing `stuff((select [...] for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '')` (Note the **, type** and **.value('.', 'nvarchar(max)')**) – asontu Jan 02 '15 at 14:24
@saharsh: http://stackoverflow.com/questions/27936466/merge-2-or-more-rows-of-a-column-if-other-column-has-same-values-text-in-sql-ser – Jan 14 '15 at 08:40

VahidNaderi · Answer 2 · 2015-01-05T07:00:37.750

1

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

edited Jan 05 '15 at 07:00

answered Jan 02 '15 at 14:18

VahidNaderi

2,448
1
23
35

Yes that was redundant, see the fixed version. – VahidNaderi Jan 05 '15 at 07:06

score 0 · Answer 3 · edited Jan 02 '15 at 12:49

0

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

edited Jan 02 '15 at 12:49

answered Jan 02 '15 at 10:51

Dgan

10,077
1
29
51

output should be 231,232 – Jan 02 '15 at 12:19
http://stackoverflow.com/questions/27936466/merge-2-or-more-rows-of-a-column-if-other-column-has-same-values-text-in-sql-ser – Jan 14 '15 at 08:42

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

3 Answers3