Query to stuff duplicate records in SQL Server?

Question

I have this table:

   +----------+----------+---------+-----------------------------------+
| Class A  | Class  B | Class C | Result_XML                        |
+----------+----------+---------+-----------------------------------+
| SUPER    | PREMIUM  | A       | <Array> <Ser ref="RF124" r="200”> |
| ECONOMIC | SEMI     | A       | <Array> <Ser ref="RF124" r="200”> |
| SUPER    | PREMIUM  | A       | <Array> <Ser ref="RF144" r="500”> |
| SUPER    | NA       | B       | <Array> <Ser ref="RF124" r="200”> |
| ECONOMIC | SEMI     | A       | <Array> <Ser ref="RF154" r="200”> |
| SUPER    | PREMIUM  | C       | <Array> <Ser ref="RF124" r="100”> |
+----------+----------+---------+-----------------------------------+

And, what I've been trying to get is something like below:

+----------+---------+--------+---------+
|  ClassA  | ClassB  | ClassC | Result  |
+----------+---------+--------+---------+
| SUPER    | PREMIUM | A      | 200,500 |
| ECONOMIC | SEMI    | A      |     200 |
| SUPER    | NA      | B      |     200 |
| SUPER    | PREMIUM | C      |     100 |
+----------+---------+--------+---------+

Basically the above is distinct list of records from first table having same result in last column and if different then stuff the results in same row like first example. So far I come up with the following but is obviously not working. Thank you in advance for any help:

SELECT DISTINCT
    ClassA, ClassB, ClassC,
    Result = (STUFF((Select Distinct ',' + E1.RESULT_XML.value('(/Array/Ser/@r)[1]', 'varchar(max)')  
                     From listtable E2 
                     Where E1.ClassA = E2.ClassA 
                       And E1.ClassB = E2.ClassB 
                       And E1.ClassC = E2.ClassC
                     FOR XML PATH(''), TYPE, ROOT).value('root[1]','nvarchar(max)'),1,1,''))
FROM 
    listtable E1

Sorry for stange looking tables...I converted the table from excel to ascii format but still not displaying correctly — Nomdeplume, May 18 '17 at 11:44

score 1 · Answer 1 · answered May 18 '17 at 12:33

You should use E2.ResultXml instead of E1.ResultXml in the stuff query.

DECLARE @T TABLE (ClassA NVARCHAR(50), ClassB NVARCHAR(50), ClassC NVARCHAR(50), ResultXML NVARCHAR(300))

INSERT @T SELECT 'SUPER','PREMIUM'  ,'A','200'--'<Array> <Ser ref="RF124" r="200”>' 
INSERT @T SELECT 'ECONOMIC','SEMI'  ,'A','200'--'<Array> <Ser ref="RF124" r="200”>' 
INSERT @T SELECT 'SUPER','PREMIUM'  ,'A','500'--'<Array> <Ser ref="RF144" r="500”>' 
INSERT @T SELECT 'SUPER','NA'       ,'B','200'--'<Array> <Ser ref="RF124" r="200”>' 
INSERT @T SELECT 'ECONOMIC','SEMI'  ,'A','200'--'<Array> <Ser ref="RF154" r="200”>' 
INSERT @T SELECT 'SUPER','PREMIUM'  ,'C','100'--'<Array> <Ser ref="RF124" r="100”>' 

SELECT * FROM @T


SELECT 
    ClassA,ClassB,ClassC,
    Result = (STUFF((Select  DISTINCT ',' + E2.RESULTXML  
                     From @T E2 
                     Where E1.ClassA = E2.ClassA 
                       And E1.ClassB = E2.ClassB 
                       And E1.ClassC = E2.ClassC
                     FOR XML PATH('')),1,1,''))
FROM 
    @T E1 
GROUP BY
    ClassA, ClassB, ClassC

Yields--->

ClassA      ClassB     ClassC  Result
ECONOMIC    SEMI       A       200
SUPER       NA         B       200
SUPER       PREMIUM    A       200,500
SUPER       PREMIUM    C       100

score 0 · Answer 2 · answered May 18 '17 at 12:41

;with cte( ClassA  , ClassB , ClassC , Result_XML)
AS
(
SELECT 'SUPER'    , 'PREMIUM'  , 'A'       , '<Array> <Ser ref="RF124" r="200”>' Union all 
SELECT 'ECONOMIC' , 'SEMI'     , 'A'       , '<Array> <Ser ref="RF124" r="200”>' Union all 
SELECT 'SUPER'    , 'PREMIUM'  , 'A'       , '<Array> <Ser ref="RF144" r="500”>' Union all 
SELECT 'SUPER'    , 'NA'       , 'B'       , '<Array> <Ser ref="RF124" r="200”>' Union all 
SELECT 'ECONOMIC' , 'SEMI'     , 'A'       , '<Array> <Ser ref="RF154" r="200”>' Union all 
SELECT 'SUPER'    , 'PREMIUM'  , 'C'       , '<Array> <Ser ref="RF124" r="100”>' 
)

,Result AS (
    SELECT ClassA
        ,ClassB
        ,ClassC
        ,CAST(REPLACE(RIGHT(Result_XML, 5), '”>', '') AS INT) AS Result_XML
    FROM cte
    )

SELECT DISTINCT ClassA
    ,ClassB
    ,ClassC
    ,STUFF((
            SELECT DISTINCT ', ' + CAST(i.Result_XML AS VARCHAR(10))
            FROM Result i
            WHERE i.ClassA = o.ClassA
                AND i.ClassB = o.ClassB
                AND i.ClassC = o.ClassC
            FOR XML PATH('')
            ), 1, 1, '') AS Result
FROM Result o
ORDER BY ClassC
    ,Result DESC

OutPut

+----------+---------+--------+---------+
|  ClassA  | ClassB  | ClassC | Result  |
+----------+---------+--------+---------+
| SUPER    | PREMIUM | A      | 200,500 |
| ECONOMIC | SEMI    | A      |     200 |
| SUPER    | NA      | B      |     200 |
| SUPER    | PREMIUM | C      |     100 |
+----------+---------+--------+---------+

score 0 · Accepted Answer · answered May 18 '17 at 13:29

I have a solution to the problem, but I'm sure it can be solved more effectively.

SELECT
    ClassA
    ,ClassB
    ,ClassC
    ,Result = (STUFF((SELECT DISTINCT
            ',' + CAST(E1.Result AS XML).value('(/Array/Ser/@r)[1]', 'varchar(max)')
        FROM [OrchestratorNotifications].[dbo].[Kuko] E2
        WHERE E1.ClassA = E2.ClassA
        AND E1.ClassB = E2.ClassB
        AND E1.ClassC = E2.ClassC
        FOR XML PATH (''), TYPE, ROOT)
    .value('root[1]', 'nvarchar(max)'), 1, 1, '')) INTO #tmp1
FROM [OrchestratorNotifications].[dbo].[Kuko] E1
ORDER BY ClassA
    ,ClassB
    ,ClassC

SELECT ClassA
    ,ClassB
    ,ClassC
    ,Result FROM (
SELECT *,ROW_NUMBER() OVER( PARTITION BY ClassA,ClassB,ClassC ORDER BY ClassA,ClassB,ClassC,w DESC) AS r1 FROM( 
SELECT
    ClassA
    ,ClassB
    ,ClassC
    ,o.Result,
    o.w, 
    ROW_NUMBER() OVER( PARTITION BY ClassA,ClassB,ClassC,o.w  ORDER BY ClassA,ClassB,ClassC,o.w) AS rownum
FROM #tmp1 a
CROSS APPLY (SELECT
        CASE
            WHEN a.Result = b.Result THEN a.Result
            ELSE a.Result + ',' + b.Result
        END Result,
            CASE
            WHEN CAST( a.Result AS INT) = CAST( b.Result AS INT) THEN  CAST( a.Result AS INT) 
            ELSE CAST( a.Result AS INT)  + CAST( b.Result AS INT) 
        END w
    FROM #tmp1 b
    WHERE a.ClassA = b.ClassA
    AND a.ClassB = b.ClassB
    AND a.ClassC = b.ClassC) o
    )y 
    where y.rownum=1
    )d 
    WHERE d.r1=1
    ORDER BY d.ClassC

DROP TABLE #tmp1

it is giving me the result exactly what I wanted-- thank you. Now I need to understand how it is actually working :-) — Nomdeplume, May 19 '17 at 08:40

Query to stuff duplicate records in SQL Server?

3 Answers3