2
VillageId     MDL        Village         PINCode

101           A1           ABC           505330
102           A1           DEF           505330
103           A1           CDF           505330
104           A2           LMN           505331
105           A2           KLM           505331
106           A4           RST           623212

I want to show duplicate records as a comma separated column in result. Duplicate record here are where MDL and PINCODE both should match.

 MDL           Village           PINCode
---------------------------------------------
  A1          ABC,DEF,CDF         505330
  A2           LMN,KLM            505331
  A4            RST               623212

So far i tried writing a function

CREATE FUNCTION dbo.ListComma 
( 
     @PINCode varchar(11),
    @MDL varchar(MAX)

) 
RETURNS VARCHAR(8000) 
AS 
BEGIN 
    DECLARE @r VARCHAR(8000) 
      SELECT @r = ISNULL(@r+', ', '') +  Village 
      FROM dbo.tblVillageData
      WHERE MDL= @MDL
        and PINCODE = @PINCode   
    RETURN @r 
END
Cœur
  • 37,241
  • 25
  • 195
  • 267
user3452210
  • 147
  • 1
  • 5
  • 17

1 Answers1

4

SQL Server:

Using STUFF and XML Path you could do this easily:

SELECT DISTINCT MDL
    ,STUFF((
            SELECT ',' + t1.Village
            FROM dbo.tblVillageData T1
            WHERE T1.MDL = T2.MDL
              AND T1.PINCode = T2.PINCode
            FOR XML PATH('')
            ), 1, 1, '') AS Village
    ,PINCode
FROM dbo.tblVillageData T2;

SQL Server Demo

MySQL:

If you are using MySQL database, then you could simply use GROUP_CONCAT to do this:

SELECT 
     MDL
    ,GROUP_CONCAT(Village) AS Village
    ,PINCode
FROM Test T2
GROUP BY MDL;

MySQL Demo

Update:

If you have multiple repeating Village value like you provided in the comment, you could easily handle them using distinct within the subquery:

SELECT Distinct  MDL
    ,STUFF((
            SELECT distinct ',' + t1.Village   --add distinct here
            FROM Test T1
            WHERE T1.MDL = T2.MDL
              AND T1.PINCode = T2.PINCode
            FOR XML PATH('')
            ), 1, 1, '') AS Village
    ,PINCode
FROM Test T2;

Duplicates Village Demo

FutbolFan
  • 13,235
  • 3
  • 23
  • 35
  • thanks for ur help, sorry i saw ur answer lately. May be i am failed in explaining u correctly. Lets say i have following records http://www.sqlfiddle.com/#!3/ed88a/2 . In this case a single row should come with village ABC, LMN, KLM – user3452210 Aug 18 '15 at 09:58
  • 1
    @user3452210 Please check out my updated answer. It should handle your duplicate village values. :) – FutbolFan Aug 18 '15 at 12:52