group_concat and distinct in mssql 2012

Question

如何在ms-sql2012中使用具有不同数据的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`

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;