group_concat and distinct in mssql 2012

Question

How can I use group_concat function with distinct data in ms sql 2012?

Is this possible for mssql? Can someone please help me to translate this mysql query to mssql? It would be a very big help. Thanks :)

SELECT
    a.`patid` AS PatNum,
    GROUP_CONCAT(DISTINCT(CONCAT(b.`adacode`)) SEPARATOR ', ') AS tx_plans,
    GROUP_CONCAT(DISTINCT(a.`provid`) SEPARATOR ', ') AS provider_ids,
    GROUP_CONCAT(DISTINCT(CONCAT(c.`last_name`, ', ', c.`first_name`)) SEPARATOR ' | ') as providers
FROM
fullproclog a
  INNER JOIN proccode b 
    ON a.`proccodeid` = b.`proccodeid`
  LEFT JOIN v_provider c 
    ON a.`provid` = c.`provider_id`
WHERE 1 = 1
    AND a.`chartstatus` = 102
    AND a.`procdate` = DATE('2020-07-02')
GROUP BY a.`patid`, a.`provid`

Answer 1

You can use STRING_AGG() with window functions:

SELECT patid,
       STRING_AGG(CASE WHEN seqnum_a = 1 THEN adacode END, ', ') AS tx_plans,
       STRING_AGG(CASE WHEN seqnum_p = 1 THEN provid, ', ') AS provider_ids,
       STRING_AGG(CASE WHEN seqnum_n = 1 THEN CONCAT(last_name, ', ', first_name)), '|') as as providers

FROM (SELECT fpc.patid AS PatNum,
             ROW_NUMBER() OVER (PARTITION BY fpc.patid, pc.adacode ORDER BY fpc.patid) as seqnum_a,
             ROW_NUMBER() OVER (PARTITION BY fpc.patid, fpc.provid ORDER BY fpc.patid) as seqnum_p,
             ROW_NUMBER() OVER (PARTITION BY fpc.patid, fpc.provid, CONCAT(p.last_name, ', ', p.first_name) ORDER BY fpc.patid) as seqnum_n
      FROM fullproclog fpc
           proccode pc
           ON fpc.proccodeid = pc.proccodeid LEFT JOIN
           v_provider p
           ON a.provid = c.pprovider_id
      WHERE fpc.chartstatus = 102 AND
            fpc.procdate = '2020-07-02'
     ) p
GROUP BY fpc.patid;