Joining a single record to a multi record table and returning one row

[John9992]

I want to write a sql query for a report that returns the following:

Resource_ID,  Resource_Name, Investment_Name, Task_Name, Date_Worked, Note, Hours

My issue?  The Notes are causing duplicative hours information....  I need to somehow rewrite this query so that all notes are concatenated into just one field so when I join, I don't duplicate the data.  The only table with multiple record data is the DWH_TIME_ENTRY_NOTE table.  The rest are single record tables.

Thanks in advance for looking at this and helping out!

~John

 

Here's my query as it currently exists:

select 
     "DWH_RES_RESOURCE"."RESOURCE_ID" as "RESOURCE_ID", 
     "DWH_RES_RESOURCE"."RESOURCE_NAME" as "RESOURCE_NAME", 
     "DWH_TME_ENTRY"."INVESTMENT_NAME" as "INVESTMENT_NAME", 
     "DWH_TME_ENTRY"."TASK_NAME" as "TASK_NAME", 
     "DWH_CMN_PERIOD_D_V"."PERIOD_START_DATE" as "DATE_WORKED", 
     "DWH_TME_ENTRY_FACTS"."TIME_HOURS" as "HOURS",
     "DWH_TME_ENTRY_NOTE"."TIMEENTRY_NOTE" as "NOTE"
   from "DWH_RES_RESOURCE" "DWH_RES_RESOURCE"
   left outer join "DWH_TME_SHEET" "DWH_TME_SHEET" on (("DWH_RES_RESOURCE"."RESOURCE_KEY" = "DWH_TME_SHEET"."RESOURCE_KEY"))
   left outer join "DWH_TME_ENTRY" "DWH_TME_ENTRY" on (("DWH_TME_SHEET"."TIMESHEET_KEY" = "DWH_TME_ENTRY"."TIMESHEET_KEY"))
   left outer join "DWH_TME_ENTRY_NOTE" on (("DWH_TME_ENTRY"."TIMEENTRY_KEY" = "DWH_TME_ENTRY_NOTE"."TIMEENTRY_KEY"))
   left outer join "DWH_TME_ENTRY_FACTS" "DWH_TME_ENTRY_FACTS" on (("DWH_TME_ENTRY"."TIMEENTRY_KEY" = "DWH_TME_ENTRY_FACTS"."TIMEENTRY_KEY"))
   left outer join "DWH_CMN_PERIOD_D_V" "DWH_CMN_PERIOD_D_V" on (("DWH_TME_ENTRY_FACTS"."PERIOD_KEY" = "DWH_CMN_PERIOD_D_V"."PERIOD_KEY") and ("DWH_CMN_PERIOD_D_V"."LANGUAGE_CODE" = 'en'))
 

 

 

[C-Box]

You could perhaps use the COALESCE sql function?!?

see here: https://www.sqlshack.com/using-the-sql-coalesce-function-in-sql-server/   or also here https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv

perhaps this could help to concat your notes as a SubQueryResult instead of joining them.

hth + regards

C-Box