sql - mssql 2012 中的 group_concat 和 distinct

Question

如何在 ms sql 2012 中使用具有不同数据的 group_concat 函数？

这对 mssql 可行吗？有人可以帮我把这个mysql查询翻译成mssql吗？这将是一个很大的帮助。谢谢 ：）

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`

标签： sqlsql-servergroup-concatstring-aggregation

Answer 1

您可以使用STRING_AGG()窗口函数：

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;