Compare and merge two comma separated fields in sql server 2008

Question

I've the following in the Table TopTenBrands:

Brands             Cust_ID
------------------------------    
Hitachi           16402, 16407    
Hitachi           16409, 16428    
JVC               19301, 19308    
LG                21001, 21007    
LG                21001, 21007, 21008    
LG                21001, 21008    
Panasonic         27909, 27912    
Philips           28501, 28518    
Philips           28513, 28516

In the above table I've a LG brand which holds three rows

LG                21001, 21007    
LG                21001, 21007, 21008    
LG                21001, 21008

But in the Output table i want the result as fallows

Brands             Cust_ID
------------------------------
Hitachi           16402, 16407    
Hitachi           16409, 16428    
JVC               19301, 19308    
LG                21001, 21007, 21008    
Panasonic         27909, 27912    
Philips           28501, 28518    
Philips           28513, 28516

I want to delete the duplicate and retain the unique ID's and I don't want ID's to group by Brand.

How can I do it?

Just curious why you don't want to move the list of numbers to a related table. It's still possible to do what you want but putting it into a table that can be indexed, cached and optimized might make more sense. — inevio, Aug 29 '12 at 09:55
Wouldn't you want to compact Hitachi and Philips as well? How do you enter data into `TopTenBrands`? If it is a query, it should be rewritten. — Nikola Markovinović, Aug 29 '12 at 10:17
C'mon! it's not fair to vote down the question just because the data structure is not normalized! We don't know the circumnstances and we are penalizing the user. The question is valid and well-formed. — Yván Ecarri, Aug 29 '12 at 10:17
@Y.Ecarri I you under stand the question perfectly, then tell us what duplicate do he wants to remove? `Brands` or `Cust_ID`? — Himanshu, Aug 29 '12 at 10:24
@hims056 Both! He wants to "flatten" the table. I would reccommend, as podiluska said, to normalize the Data. Krešimir Lukin provides a way to do that. At the end he shoudl have pairs (Brand, Cust_Id) with unique Cust_Ids (according with 1NF) — Yván Ecarri, Aug 29 '12 at 10:32
@Y.Ecarri If he wants to remove both duplicates then why there are two `Hitachi` and `Philips` and only one `LG` brands? What is logic behind it? — Himanshu, Aug 29 '12 at 10:37
@NikolaMarkovinović Hmm, OP went offline after putting a question. — Himanshu, Aug 29 '12 at 10:41
@hims056 Yes, you are right. There is no logic behind that. No wait! there is! LG rows are formed by Cust_Ids that are already in other rows while Hitachi rows no. Well, I agree... the correct answer would be one row for each Brand and Cust_Id — Yván Ecarri, Aug 29 '12 at 11:01
Sorry for the late replay i know that data in the table is not normalized. I want to retain both hitachi as Cust_ID are not dublicat in that but where as IN LG Brand Cust_ID are repeting. So i want to retain unique ID fro the Brand. Hear Brand is not an PK. As i am new to the SQL Sp. — user1632718, Aug 29 '12 at 11:02
@user1632718 If you data Structure is like [this](http://sqlfiddle.com/#!3/21e7a/2). Then it is possible what you want to do. — Himanshu, Aug 29 '12 at 11:07

score 1 · Answer 1 · answered Aug 29 '12 at 10:30

1

If you want to remove duplicate Brands, you can do it like this:

SELECT brand, 
(SELECT top 1 cust_ID 
        FROM TopTenBrands 
        WHERE brand = ttb.brand) 
FROM TopTenBrands ttb
GROUP BY brand;

If you don't want to use GROUP BY

SELECT DISTINCT brand, 
(SELECT top 1 cust_ID 
        FROM TopTenBrands 
        WHERE brand = ttb.brand) AS cust_ID
FROM TopTenBrands ttb;

See this SQLFiddle

answered Aug 29 '12 at 10:30

Himanshu

31,810
31
111
133

thanks for the Replay I know that table is not in organized propery. and if i do the group by then i will miss the data (Cust_ID) only i want to remove the dublicate Cust_ID from the Table – user1632718 Aug 29 '12 at 10:53
@user1632718 It is ok. But what do you exactly want to do? Do you want to remove duplicate Brands or Cust_ID or both? – Himanshu Aug 29 '12 at 10:56
I just want to delete dublicate Cust_ID – user1632718 Aug 29 '12 at 11:06
@user1632718 but there is no duplicate value in `Cust_ID` column shown in your question. – Himanshu Aug 29 '12 at 11:32
As you can see LG Brand has Cust_ID as 21001,21007 in first row these two ID's are same as they have One Resource_ID Like T0001 and now in secound row CUST_ID has 21001,21007,21008 in this they have Resource_ID Like T0001,T0002 and These both resource belongs to the same groups like GR001 so I need to Ignore first record as they belongs to the same group with 2 difrent resouce – user1632718 Aug 29 '12 at 11:42
@user1632718 To get proper answers please edit your question and add all fields and information in it. – Himanshu Aug 29 '12 at 11:45

score 1 · Answer 2 · answered Aug 29 '12 at 12:03

Try this

Declare @t  table(Brands Varchar(20),Cust_Id Varchar(100))
Insert Into @t 
Select 'Hitachi','16402, 16407' Union ALL Select 'Hitachi','16409, 16428' Union All
Select 'JVC','19301, 19308' Union All Select 'LG','21001, 21007' Union All    
Select 'LG','21001, 21007, 21008'  Union All Select 'LG','21001, 21008' Union All
Select 'Panasonic','27909, 27912 ' Union All Select 'Philips','28501, 28518'  Union All  
Select 'Philips','28513, 28516'


;WITH CTE AS
(
SELECT 
Rn = ROW_NUMBER() OVER(PARTITION BY LTRIM(RTRIM(Y.SplitCust_Id)),Brands ORDER BY (SELECT 1))
 ,X.Brands
 ,X.Cust_Id
 ,SplitCust_Id =LTRIM(RTRIM(Y.SplitCust_Id))
 FROM(SELECT *,CAST('<X>'+REPLACE(F.Cust_Id,',','</X><X>')+'</X>' AS XML) AS xmlfilter
      FROM @t F)X CROSS APPLY
     (SELECT fdata.D.value('.','varchar(50)') AS SplitCust_Id 
      FROM X.xmlfilter.nodes('X') AS fdata(D)) Y
)
,CTE_FINDProductsToMerge AS
( 
    SELECT Distinct Brands,SplitCust_Id 
    FROM CTE 
    WHERE Brands NOT IN (SELECT Brands FROM @t WHERE Rn=1)
)
,CTE2 AS
(
    SELECT c1.Brands, Cust_Id  = STUFF(
            ( SELECT ',' + CAST(SplitCust_Id AS VARCHAR(1000)) 
                FROM CTE_FINDProductsToMerge c2
                WHERE  c1.Brands = c2.Brands
                FOR XML PATH('')),1,1,''
            )
    FROM CTE_FINDProductsToMerge c1
    GROUP BY Brands
)

SELECT Brands,Cust_Id FROM @t  WHERE Brands NOT IN (SELECT Brands From CTE2)
UNION ALL
SELECT Brands,Cust_Id From CTE2
ORDER BY 2

Output:

Brands  Cust_Id
Hitachi 16402, 16407
Hitachi 16409, 16428
JVC 19301, 19308
LG  21001,21007,21008
Panasonic   27909, 27912 
Philips 28501, 28518
Philips 28513, 28516

score 0 · Answer 3 · edited May 23 '17 at 11:58

0

You will have to first group concat results and insert them into temp table. Then you can iterate throug temp table and for each row do something like this and select distinct separator delimited rows and update temp table for that row.

I don't know your concrete data model, but I think you can organize your table schema efficiantly.

edited May 23 '17 at 11:58

Community

1
1

answered Aug 29 '12 at 10:09

Krešimir Lukin

399
1
7

Compare and merge two comma separated fields in sql server 2008

3 Answers3

See this SQLFiddle