merger column value of duplicate records

Question

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

[Bad habits : Putting NOLOCK everywhere](https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere) — Thom A, Dec 27 '21 at 13:37

score 1 · Accepted Answer · answered Dec 27 '21 at 13:19

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

" 'string_agg' is not a recognized built-in function name. " < error — Falcon, Dec 27 '21 at 13:25

merger column value of duplicate records

1 Answers1