Joby Joby - 5 months ago 14
SQL Question

How to get all the distinct data from the columns pipe delimited

I am using the below query to get the data

SELECT NMA.MsgAddress,CC.PagerNum,CC.Email,CC.VoiceNum
FROM [Table1] GEP (NOLOCK)
INNER JOIN [Table2] CC (NOLOCK)
ON GEP.PersonID = CC.PersonID
LEFT OUTER JOIN [Table3] NME (NOLOCK)
ON GEP.PAN = NME.Cardnumber
AND NME.Status = 2 --ACTIVE (Enrolment Status)
INNER JOIN [Table4] NMA (NOLOCK)
ON NME.MsgAddressUID = NMA.MsgAddressUID
AND NMA.Status = 1 --ACTIVE (Address Status)
INNER JOIN [Table5] NMAT (NOLOCK)
ON NMA.MsgAddressTypeUID = NMAT.MsgAddressTypeUID
AND NMAT.MsgAddressType IN ('MobileNumber','EMAIL')
WHERE GEP.PAN IN (TEST)


The output that I get is as below

enter image description here

Need to get the distinct result set pipe delimited as below.

9856235687|test@Testing.com|698754321|Prakash@gmail.com|123121212


The MsgAddress column is the one that's have multiple entries.

Answer

May be something like this

WITH SomeCTE (MsgAddress,PagerNum,Email,VoiceNum)
AS (
    SELECT NMA.MsgAddress,CC.PagerNum,CC.Email,CC.VoiceNum
    FROM [Table1] GEP (NOLOCK)
    INNER JOIN [Table2] CC (NOLOCK) 
        ON GEP.PersonID = CC.PersonID
    LEFT OUTER JOIN [Table3] NME (NOLOCK) 
        ON GEP.PAN = NME.Cardnumber 
        AND NME.Status = 2 --ACTIVE (Enrolment Status)
    INNER JOIN [Table4] NMA (NOLOCK) 
        ON NME.MsgAddressUID = NMA.MsgAddressUID 
        AND NMA.Status = 1 --ACTIVE (Address Status)
    INNER JOIN [Table5] NMAT (NOLOCK) 
        ON NMA.MsgAddressTypeUID = NMAT.MsgAddressTypeUID 
        AND NMAT.MsgAddressType IN ('MobileNumber','EMAIL')
    WHERE GEP.PAN IN (TEST)
)

SELECT STUFF((SELECT '|' + MsgAddress
              FROM SomeCTE 
              FOR XML PATH('')) ,1,1,'') 
       + '|' + MAX(CC.PagerNum) + '|' + MAX(CC.Email) + '|' + MAX(CC.VoiceNum) As Result
FROM SomeCTE 
GROUP BY PagerNum,Email,VoiceNum
Comments