Want to remove string_agg() function duplicate values

Question

My procedure:

  declare @unit  int
  declare  @department int
  declare @section int
  declare @block int
  declare @empId int
  declare  @fromdate date
  declare @todate date

  if @unit = 0 set @unit = null
  if @department = 0 set @department = null
  if @section = 0 set @section = null
  if @block = 0 set @block = null
  if @empId = 0 set @empId = null
  set @fromdate = '01/apr/2023'
  set @todate = '20/apr/2023'

begin       
  SELECT       
      b2_department_info.department, b2_section_info.section,b2_block_info.block,b2_emp_basic.emp_cardno,b2_emp_basic.emp_name,      
      string_agg(day(b2_floor_production_list.prod_date),',') as ProdDate,b2_style_info.style,b2_process_info.process_name,
      sum(b2_floor_production_list.quantity) as Quantity,
      b2_company_info.company_name,b2_building_info.building_name, @fromdate as fromdate, @todate as todate
  FROM            
      b2_floor_production_list INNER JOIN
      b2_process_info ON b2_floor_production_list.process = b2_process_info.process_id INNER JOIN
      b2_style_info ON b2_process_info.style = b2_style_info.style_id INNER JOIN
      b2_emp_basic ON b2_floor_production_list.emp_id = b2_emp_basic.emp_id INNER JOIN
      b2_department_info ON b2_emp_basic.department = b2_department_info.deptId INNER JOIN
      b2_section_info ON b2_emp_basic.section = b2_section_info.section_id INNER JOIN
      --b2_department_info on b2_department_info.deptId = b2_section_info.department inner join
      b2_block_info ON b2_emp_basic.block = b2_block_info.blockId INNER JOIN
      b2_designation_info ON b2_emp_basic.designation = b2_designation_info.desigId INNER JOIN
      b2_building_info ON b2_emp_basic.unit = b2_building_info.building_id inner join
      b2_company_info on b2_building_info.company=b2_company_info.company_id                      
  where (b2_floor_production_list.prod_date >= CONVERT(date,@fromdate))
  and (b2_floor_production_list.prod_date <= CONVERT(date,@todate))
  and (b2_floor_production_list.emp_id = @empId or @empId is null)
  and (b2_building_info.building_id = @unit or @unit is null)
  and (b2_department_info.deptId = @department or @department is null)
  and (b2_floor_production_list.section = @section or @section is null)
  and (b2_block_info.blockId = @block or @block is null)
  group by b2_department_info.department, b2_section_info.section,b2_block_info.block,b2_emp_basic.emp_cardno,b2_emp_basic.emp_name,b2_style_info.style,b2_process_info.process_name,b2_company_info.company_name,b2_building_info.building_name

Facing following problems: When i execute this procedure it returns string_agg() duplicates values see image facing problems

i try string_agg(distinct (columnname)) function. but not works at all.

Please tag the actual product you're using. Different products have different features that might be relevant here. — Tangentially Perpendicular, Apr 20 '23 at 06:35
Does this answer your question? [Get unique values using STRING\_AGG in SQL Server](https://stackoverflow.com/questions/50589064/get-unique-values-using-string-agg-in-sql-server) — markalex, Apr 20 '23 at 07:10
You have to use some other way of getting distinct values. For example by doing correlated subquery — siggemannen, Apr 20 '23 at 07:13
*"mssql server 2018"* there is no such thing, @NurHossainSujon . There were releases in 2017 and 2019, but there was not one in 2018. — Thom A, Apr 20 '23 at 08:22

score 0 · Answer 1 · answered Apr 20 '23 at 09:41

Possible way to resolve issue - get distinct values (prod_date) by subquery. For example:

SELECT       
      b2_department_info.department, b2_section_info.section,b2_block_info.block,b2_emp_basic.emp_cardno,b2_emp_basic.emp_name,      
      string_agg(day(b2_floor_production_list.prod_date),',') as ProdDate,b2_style_info.style,b2_process_info.process_name,
      sum(b2_floor_production_list.quantity) as Quantity,
      b2_company_info.company_name,b2_building_info.building_name, @fromdate as fromdate, @todate as todate
  FROM
--
     (select  b2_floor_production_list.process,b2_floor_production_list.emp_id
            ,b2_floor_production_list.prod_date
            ,b2_floor_production_list.section
            ,sum(b2_floor_production_list.quantity) as Quantity
         from b2_floor_production_list
         group by process,emp_id,section,prod_date) as 
--
      b2_floor_production_list INNER JOIN
      b2_process_info ON b2_floor_production_list.process = b2_process_info.process_id INNER JOIN
      b2_style_info ON b2_process_info.style = b2_style_info.style_id INNER JOIN
      b2_emp_basic ON b2_floor_production_list.emp_id = b2_emp_basic.emp_id INNER JOIN
      b2_department_info ON b2_emp_basic.department = b2_department_info.deptId INNER JOIN
      b2_section_info ON b2_emp_basic.section = b2_section_info.section_id INNER JOIN
      --b2_department_info on b2_department_info.deptId = b2_section_info.department inner join
      b2_block_info ON b2_emp_basic.block = b2_block_info.blockId INNER JOIN
      b2_designation_info ON b2_emp_basic.designation = b2_designation_info.desigId INNER JOIN
      b2_building_info ON b2_emp_basic.unit = b2_building_info.building_id inner join
      b2_company_info on b2_building_info.company=b2_company_info.company_id                      
  where (b2_floor_production_list.prod_date >= CONVERT(date,@fromdate))
  and (b2_floor_production_list.prod_date <= CONVERT(date,@todate))
  and (b2_floor_production_list.emp_id = @empId or @empId is null)
  and (b2_building_info.building_id = @unit or @unit is null)
  and (b2_department_info.deptId = @department or @department is null)
  and (b2_floor_production_list.section = @section or @section is null)
  and (b2_block_info.blockId = @block or @block is null)
  group by b2_department_info.department, b2_section_info.section
      ,b2_block_info.block,b2_emp_basic.emp_cardno
      ,b2_emp_basic.emp_name,b2_style_info.style
      ,b2_process_info.process_name,b2_company_info.company_name
      ,b2_building_info.building_name

What effects may cause NULL values in emp_id, section - you can check additionally.

Want to remove string_agg() function duplicate values

1 Answers1