Hi all,
I hope everyone is staying safe and hopefully optimistic right now.
Myself and a colleague are working on code to pull out several functions from our gift officer's portfolio screens including "Natural Partners" aka a type of Plan Worker used to denote Board/Senior Stakeholder relationships with prospects.
In the System Tables the code reads: Select DISTINCT STUFF((Select DISTINCT ', '+worker_display_name FROM BI.VT_Plan_Worker WHERE worker_role_no=4 and plan_no=@plan_no For XML PATH ('')),1,1,'') FROM BI.VT_PLAN_WORKER where plan_no=@plan_no AND worker_role_no=4
Referencing the plan_no reflexively is something we're having difficulty with within the STUFF statement. So far I have:
USE impresario
SELECT DISTINCT plan_no, STUFF((Select DISTINCT ', '+convert(varchar (10),customer_no) FROM TX_CUST_PLAN WHERE role_no=4 For XML PATH ('')),1,1,'') FROM TX_CUST_PLAN WHERE role_no=4
Any ideas?
Thank you!Caro
Hi Caro,
I'm not sure what it is that you're trying to do? Are you trying to link back to the plan worker table to display a comma delimited list of workers? What do you want it to look like in the end?
Thanks,
Sara
That's correct - I want all of the plan workers to appear like "John Smith, Joan Adams, Robert Thomson" in one cell.