0

This query:

SELECT DISTINCT
    PT.PRODUCT AS PRODUCT, PT.TEXT_CODE AS TEXT_CODE, 
    PHT.PHRASE AS F_PHRASE
FROM 
    PRODUCT_TEXT PT
LEFT JOIN 
    PHRASE_LINK PHL ON PT.TEXT_CODE = PHL.TEXT_CODE
INNER JOIN 
    PHRASE_TRANSLATIONS PHT ON PHL.PHRASE_ID = PHT.PHRASE_ID
WHERE 
    PT.DATA_CODE = 'MANU' AND PHT.LANGUAGE = 'EN'

UNION ALL

SELECT DISTINCT
    PT.F_ALIAS AS PRODUCT, PT.TEXT_CODE AS TEXT_CODE, 
    PHT.PHRASE AS F_PHRASE
FROM 
    PROD_ALIAS_TEXT PT
LEFT JOIN 
    PHRASE_LINK PHL ON PT.TEXT_CODE = PHL.TEXT_CODE
INNER JOIN 
    PHRASE_TRANSLATIONS PHT ON PHL.PHRASE_ID = PHT.PHRASE_ID
WHERE 
    PT.DATA_CODE = 'MANU' AND PHT.LANGUAGE = 'EN'

returns this output:

F_PRODUCT       F_TEXTCODE       F_PHRASE 
-----------------------------------------------------
A               MANU001          TEST1      
A               MANU002          TEST2      
B               MANU003          XYZ
C               MANU001          ABC
C               MANU005          DEF

Now I want to concatenate the F_PHRASE into a single row for the products which contains more then one F_TEXTCODE. Not for all the products.

Sample desired output:

F_PRODUCT       F_TEXTCODE              F_PHRASE 
-----------------------------------------------------
A               MANU001,MANU002          TEST1,TEST2      
B               MANU003                  XYZ
C               MANU001,MANU005          ABC,DEF

Please give some better way to achieve this. The table contains 1 lakh records.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ram
  • 727
  • 2
  • 16
  • 33

0 Answers0