Replacing duplicates while transpose in Teradata

Analytics
Enthusiast

Replacing duplicates while transpose in Teradata

Hi,

I have two tables as below.

TBL_RULES






TBL_NM VLD_ID COL_NM
TABLE1 1000 COL_1
TABLE1 1001 COL_2
TABLE1 1002 COL_2
TABLE1 1003 COL_3

TABLE_LOG







TBL_NM VLD_ID REC_NBR SUCCESS_FLAG
TABLE1 1000 1 N
TABLE1 1001 1 N
TABLE1 1002 1 N

Success flag will be updated based on certain validation rules. Intention is to create a column to find which columns from a table for a row is failing the validations. I am using below query.

select LG.REC_NBR, 
RTRIM(XMLAGG(TRIM(VR.COL_NM) || ': ' ||TRIM(LG.SUCCESS_FLAG) || ','
ORDER BY LG.VALIDATION_ID
) (VARCHAR(10000)),', ') AS VLD_DSC
from TBL_RULES VR
left join TABLE_LOG LG on LG.TBL_NM = VR.TBL_NM and LG.VLD_ID= VR.VLD_ID GROUP BY 1

The result is 





REC_NBR VLD_DSC
1 COL1: N, COL2: N, COL2: N

But I want COL2 status only once. How can i modify my query. The intended result is 





REC_NBR VLD_DSC
1 COL1: N, COL2: N
1 REPLY
Enthusiast

Re: Replacing duplicates while transpose in Teradata

Making Tables Proper

TBL_RULES  
TBL_NM VLD_ID COL_NM
TABLE1 1000 COL_1
TABLE1 1001 COL_2
TABLE1 1002 COL_2
TABLE1 1003 COL_3

TABLE_LOG   
TBL_NM VLD_ID REC_NBR SUCCESS_FLAG
TABLE1 1000 1 N
TABLE1 1001 1 N
TABLE1 1002 1 N

Current Result

REC_NBR VLD_DSC
1 COL1: N, COL2: N, COL2: N

Intended

REC_NBR VLD_DSC
1 COL1: N, COL2: N