mssql 2012中的concat和distinct组

6ljaweal 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(412)

在ms sql 2012中，如何使用具有不同数据的group\u 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`

sql sql-server group-concat string-aggregation

来源：https://stackoverflow.com/questions/63071662/group-concat-and-distinct-in-mssql-2012

1条答案

按热度按时间

xoefb8l81#

你可以用 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;

赞(0）回复(0）举报 2021-07-24

我来回答

mssql 2012中的concat和distinct组

1条答案

相关问题

热门标签

最新问答