CODEWITHSUNDEEP
sqlsql-servert-sql
Karthik
Karthik

Reputation: 41

How to eliminate duplicate and merge column value to single text in SQL Server

I am trying to join three table and get the results, however one of the table has multiple event_code for the same CSO_Item_key which is resulting in duplicate records.

Table 1

Entry Date      Cso Item Key    Fail Code
8/1/2018 4:28   BLXB796201      CSL120
8/1/2018 4:40   BLXB799101      CLL250
8/1/2018 4:55   BLXB803001      CMS130
8/1/2018 5:08   BLXB806201      CNE100

Table 2

Cso Item Key    Event Code
BLXB796201      GTS
BLXB796201      LC28
BLXB796201      SDR4
BLXB799101      GTS
BLXB799101      LC28
BLXB799101      SDR4
BLXB803001      GTS
BLXB803001      LC28
BLXB803001      SDR4
BLXB806201      GTS
BLXB806201      LC28
BLXB806201      SDR4

Table 3

Fail Code  Desc
CSL120     Bad Part
CLL250     Unit Scrapped
CNE100     OS Reinstall
CBN101     NTF

Expected Result:

Entry_Date     Cso_Item_Key Fail_Code   Desc         Event_Code
8/1/2018 4:28   BLXB796201   CSL120   Bad Part       GTS,LC28,SDR4
8/1/2018 4:40   BLXB799101   CLL250   Unit Scrapped  GTS,LC28,SDR4
8/1/2018 4:55   BLXB803001   CMS130   Null           GTS,LC28,SDR4
8/1/2018 5:08   BLXB806201   CNE100   OS Reinstall   GTS,LC28,SDR4

enter image description here

Upvotes: 0

Views: 76

Answers (2)

Ryan Wilson
Ryan Wilson

Reputation: 10765

Here is another possible solution using the sql-server function STUFF and FOR XML PATH():

--Declare temp tables to simulate data given in post
DECLARE @tempTable1 TABLE([EntryDate] DateTime, [Cso Item Key] varchar(100), [Fail Code] varchar(50))
DECLARE @tempTable2 TABLE([Cso Item Key] varchar(100), [Event Code] varchar(50))
DECLARE @tempTable3 TABLE([Fail Code] varchar(50), [Desc] varchar(MAX))

--Insert the sample data
INSERT INTO @tempTable1
([EntryDate], [Cso Item Key], [Fail Code])
VALUES('2018-08-01 04:28:00', 'BLXB796201', 'CSL120'), ('2018-08-01 04:40:00', 'BLXB799101', 'CLL250'), ('2018-08-01 04:55:00', 'BLXB803001', 'CMS130'),
        ('2018-08-01 05:08:00', 'BLXB806201', 'CNE100')

INSERT INTO @tempTable2
([Cso Item Key], [Event Code])
VALUES('BLXB796201', 'GTS'), ('BLXB796201', 'LC28'), ('BLXB796201', 'SDR4'), ('BLXB799101', 'GTS'), ('BLXB799101', 'LC28'), 
('BLXB799101', 'SDR4'), ('BLXB803001', 'GTS'), ('BLXB803001', 'LC28'), ('BLXB803001', 'SDR4'), ('BLXB806201', 'GTS'), ('BLXB806201', 'LC28'), ('BLXB806201', 'SDR4')


INSERT INTO @tempTable3
([Fail Code], [Desc])
VALUES('CSL120', 'Bad Part'), ('CLL250', 'Unit Scrapped'), ('CNE100', 'OS Reinstall'), ('CBN101', 'NTF')

--The query, you can LEFT Join table 1 and table 3 on the Fail Code
--The sub query uses Group By and STUFF to produce a single row with all Event_Codes
--For each unique Cso Item Key, which can then be Inner Joined with table 1's Cso Item Key column
SELECT t1.[EntryDate], t1.[Cso Item Key], t3.[Fail Code], t3.[Desc], x.[Event_Code]
FROM @tempTable1 AS t1
LEFT JOIN @tempTable3 AS t3
ON t3.[Fail Code] = t1.[Fail Code]
INNER JOIN
(
    SELECT tt2.[Cso Item Key],
       STUFF((SELECT ',' + t2.[Event Code]
       FROM @tempTable2 AS t2
       WHERE t2.[Cso Item Key] = tt2.[Cso Item Key]
       FOR XML PATH('')), 1, 1, '') AS [Event_Code]
    FROM @tempTable2 AS tt2
    GROUP BY tt2.[Cso Item Key] 
) AS x
ON x.[Cso Item Key] = t1.[Cso Item Key]

Upvotes: 2

GigiS
GigiS

Reputation: 66

-- @-tables 1..3 are defined with your data
declare @table_1 table([Entry Date] datetime, [Cso Item Key] varchar(20), [Fail Code] varchar(10))
Insert Into @table_1 Values('8/1/2018 4:28','BLXB796201','CSL120'),('8/1/2018 4:40','BLXB799101','CLL250'),
('8/1/2018 4:55','BLXB803001','CMS130'),('8/1/2018 5:08','BLXB806201','CNE100')

declare @table_2 table([Cso Item Key] varchar(20), [Event Code] varchar(10))
Insert into @table_2 Values('BLXB796201','GTS'),('BLXB796201','LC28'),('BLXB796201','SDR4'),('BLXB799101','GTS' ),
('BLXB799101','LC28'),('BLXB799101','SDR4'),('BLXB803001','GTS' ),('BLXB803001','LC28'),('BLXB803001','SDR4'),
('BLXB806201','GTS' ),('BLXB806201','LC28'),('BLXB806201','SDR4')

declare @table_3 table([Fail Code] varchar(10), [Desc] varchar(50))
Insert Into @table_3 Values('CSL120','Bad Part'),('CLL250','Unit Scrapped'),('CNE100','OS Reinstall'),('CBN101','NTF')

--Here is the FINAL query (what u ask for)
Select T1.[Entry Date], T1.[Cso Item Key], T1.[Fail Code], T3.[Desc],
Replace((Select T2.[Event Code] + ',' From @table_2 T2 Where T2.[Cso Item Key] = T1.[Cso Item Key] For XML Path('')) + ',', ',,', '')
From @table_1 T1
Left Outer Join @table_3 T3 On T3.[Fail Code] = T1.[Fail Code]

Upvotes: 0

Related Questions