0

I have written query by combining two tables and result looks like below mentioned

Result Set

recipient_email recipient_name accrual_deadline CCA
siva@gmail.com siva jun 24 reddy@gmail.com
siva@gmail.com siva jun 24 sa@gmail.com
mohan@gmail.com mohan jun 24 ma@gmail.com
mohan@gmail.com mohan jun 24 run@gmail.com

Query:

 SELECT te.email AS recipient_email
    ,te.employee_name AS recipient_name
    ,(
        SELECT CONVERT(VARCHAR, DATEADD(d, - 3, MAX(TRANSACTION_DATE)), 107)
        FROM [POT].[vw_Calendar]
        WHERE CURRENT_FISCAL_YEAR_FLAG = 'Y'
            AND CURRENT_FISCAL_QUARTER_FLAG = 'Y'
        ) AS accrual_deadline
    ,'GR' AS emp_type
    ,te1.email AS CostCenterAnalyst FROM [POT].[vw_POT_Open_PO] op 
    LEFT JOIN POT.vw_Employee te ON te.Employee_Number = op.Goods_Recipient_Emp_ID
    AND te.flag_Status = 'A'
    JOIN POT.vw_Employee te1 ON te1.Employee_Number = op.pocostcenteranlystid 
    LEFT JOIN [POT].[tbl_POT_Open_PO_Accrual_Reminder_History] opair ON DATEPART(month, GETDATE()) + DATEPART(year, GETDATE()) = DATEPART(month, [Mail_Sent_On]) + DATEPART(year, [Mail_Sent_On])
    AND [Recipient_Email] = te.email
    AND opair.Flag_Active = 1
    AND [Reminder_Type] = 'Accrual_Input' 
    WHERE te.email IS NOT NULL
    AND opair.Recipient_Email IS NULL
    AND te.Email IN (
        'siva@amat.com'
        ,'mohan@amat.com'
        )  GROUP BY te.email
    ,te.employee_name
    ,te1.email

I have to group the recipient_email and result should display like below

recipient_email recipient_name accrual_deadline CCA
siva@gmail.com siva jun 24 reddy@gmail.com; sa@gmail.com
mohan@gmail.com mohan jun 24 ma@gmail.com; run@gmail.com
Dale K
  • 25,246
  • 15
  • 42
  • 71
Sivamohan Reddy
  • 436
  • 1
  • 8
  • 26
  • This doesn't look like your want `CONCAT_WS`, which concatenates expressions for the current row, but `STRING_AGG` with *aggregates* strings. – Thom A Jul 06 '22 at 09:28
  • I'm fine with the function, but the result should looks like I mentioned – Sivamohan Reddy Jul 06 '22 at 10:03
  • Does this answer your question? [Comma separated results in SQL](https://stackoverflow.com/questions/18870326/comma-separated-results-in-sql) – Thom A Jul 06 '22 at 10:04
  • This question is differ from the existing question, don't duplicate it – Sivamohan Reddy Jul 06 '22 at 10:59
  • Can you explain *how* it's different? If you can, I'm happy to reopen, but I do agree with @Charlieface at the moment (and it's why I proposed the duplicate myself). I am sure Charlieface will be happy to cast a reopen vote too, if you can explain *why* this isn't a duplicate. – Thom A Jul 06 '22 at 11:00
  • Looks like you just need to remove `te1.email` from the `group by` and the do `STRING_AGG(te1.email, '; ')` – Charlieface Jul 06 '22 at 11:05
  • What do you mean by it "didn't work out"? Also, you need to ping someone to make them aware you have replied, Sivamohan ( @Charlieface ) . – Thom A Jul 06 '22 at 11:46

0 Answers0