How To use generate row number with group by option

Question

The Input table DEDUPE

SOURCE  Legacy_vendor   Vendor  Condition application   Condition type  Doc type    
MX_PRISMA_PDN   9215217 1012473 EF                   ZNE1             ZMRO  
MX_PRISMA_PDN   9215217 1012473 EF                   ZNE1             ZNB   
MX_PRISMA_PDN   9215217 1012473 EF                   ZNE1             ZSRM

Output Should like

SOURCE  Legacy_vendor   Vendor  Condition application   Condition type  Doc type    
MX_PRISMA_PDN   9215217 1012473 EF                   ZNE1      ZMRO,ZNB,ZSRM

You need to tell us which DBMS are you using? Postgres? Oracle? — , Mar 20 '17 at 08:11

score 0 · Answer 1 · answered Mar 20 '17 at 09:11

BEGIN TRAN
CREATE TABLE  #DEDUPE (SOURCE NVARCHAR(50),Legacy_vendor BIGINT,Vendor BIGINT ,[Condition application]  NVARCHAR(10),[Condition type] NVARCHAR(20),[Doc type]  NVARCHAR(20))


INSERT INTO #DEDUPE
SELECT 'MX_PRISMA_PDN' ,9215217, 1012473,'EF','ZNE1','ZMRO' UNION ALL
SELECT 'MX_PRISMA_PDN', 9215217, 1012473,'EF','ZNE1','ZNB' UNION ALL
SELECT 'MX_PRISMA_PDN', 9215217, 1012473,'EF','ZNE1','ZNB' 


    SELECT
    C.SOURCE,
    C.Legacy_vendor,C.Vendor,C.[Condition application],C.[Condition type],
        STUFF((  
                SELECT  ', ' +  CONVERT(Nvarchar,CP.[Doc type])
                FROM  
                #DEDUPE CP
                WHERE
                    C.SOURCE = CP.SOURCE
                    FOR XML PATH('')), 1, 2, '') [Doc type]
    FROM
    #DEDUPE C 
    GROUP BY    C.SOURCE,
    C.Legacy_vendor,C.Vendor,C.[Condition application],C.[Condition type]

DROP TABLE #DEDUPE
ROLLBACK TRAN

score -1 · Answer 2 · answered Mar 20 '17 at 08:07

Assuming SQL Server: You can do it using XML and Stuff

SELECT DISTINCT SOURCE,Legacy_vendor,Vendor,[Condition application],[Condition type],STUFF((SELECT ','+[Doc type]

            FROM Table1 T1
             WHERE T1.SOURCE=T2.SOURCE
                AND T1.Legacy_vendor=T2.Legacy_vendor
                AND T1.Vendor=T2.Vendor
             FOR XML PATH('')
            ), 1, 1, '')
             AS [Doc type]
FROM Table1 T2

Demo

beejm · Answer 3 · 2017-03-20T08:29:41.700

-1

For SQL Server

select source, legacy_vendor, vendor, condition_application, condition_type,
doctype = 
 STUFF((    SELECT ' '+ SUB.doctype AS [text()]  
FROM DEDUPE SUB  
WHERE  sub.source = main.source and  sub.legacy_vendor = main.legacy_vendor and
       sub.vendor = main.vendor and sub.condition_application= main.condition_application and sub.condition_type = main.condition_type
FOR XML PATH('') 
), 1, 1,'')  
 from DEDUPE main
 group by source, legacy_vendor, vendor, condition, condition_type

I used STUFF to join all the doc types in 1 column

edited Mar 20 '17 at 08:29

answered Mar 20 '17 at 08:07

beejm

2,381
1
10
19

Which dbms is this for? – jarlh Mar 20 '17 at 08:24

How To use generate row number with group by option

3 Answers3