merger column value of duplicate records

December 27, 2021

I have a query that returns records to me, the record is duplicated when it finds more than 1 value

i would like to merge duplicate records but i dont know how

I’ve heard about aggregate functions, should I go in this direction? I don’t really know how to do it

SELECT distinct LCM.eFolderID, LCM.ContractNumber
        ,(bU.FirstName + ' ' + bU.Surname) as WeryfikacjaUmowa
        --,COUNT(LCM.ContractNumber)
  FROM Metastorm.dbo.LeasingContractsMap LCM with(nolock)
  left join Metastorm.dbo.eEvent eE with(nolock) on eE.eFolderID = LCM.EFOLDERID and eE.eActionName = 'AssignToContractVerificationAct'
  left join Metastorm.dbo.bpm_Users bU with(nolock) on bU.eUserName = eE.eUserName
  where LCM.ContractNumber not like '%purged%'
  and LCM.ContractNumber = '37/1276/20'

result

result i want

>Solution :

You can use string_agg to aggregate string columns when using group by in sql server.

Try this:

with u as
(SELECT distinct LCM.eFolderID, LCM.ContractNumber
            ,(bU.FirstName + ' ' + bU.Surname) as WeryfikacjaUmowa
            --,COUNT(LCM.ContractNumber)
      FROM Metastorm.dbo.LeasingContractsMap LCM with(nolock)
      left join Metastorm.dbo.eEvent eE with(nolock) on eE.eFolderID = LCM.EFOLDERID and eE.eActionName = 'AssignToContractVerificationAct'
      left join Metastorm.dbo.bpm_Users bU with(nolock) on bU.eUserName = eE.eUserName
      where LCM.ContractNumber not like '%purged%'
      and LCM.ContractNumber = '37/1276/20')
select eFolderID, ContractNumber, string_agg(WeryfikacjaUmowa, ' ') as WeryfikacjaUmowa
from u 
group by eFolderID, ContractNumber