时间戳

首页 > 解决方案 > 在 SQL Server 中使用 XML PATH 命令将多个值组合到一个单元格中

sql-server - 在 SQL Server 中使用 XML PATH 命令将多个值组合到一个单元格中

问题描述

我需要在 2 列中组合多个值以显示enter code >here在同一行中，由一个共同值（即电子邮件enter code >here地址、ID） 汇总

我已经能够通过使用“FOR XML PATH (''),1,1'')”的一列来完成此操作。但是，当我尝试在另一列上使用完全相同的语法和命令时，它不会合并。因此，我得到 A 列 = 单个值，> 和 B 列 = B1、B2、B3 ..

Declare @StartDate DateTime, @EndDate DateTime  
set @StartDate = Getdate() - 1  
set @EndDate = GetDate()  
SELECT DISTINCT  
pat.email as ADDR,  
pat.fname as INS1,  
STUFF((SELECT ', ' + right(rx.script_no,4)  
FROM cprx rx  
WHERE disp.rx_id = rx.rx_id  
and disp.rxdisp_id = rx.last_rxdisp_id  
and (rx.refills_left is not NULL AND Not(rx.refills_left between -0.1  
and 0.1))  
and len(rx.script_no) = 7  
FOR XML PATH('')),1,1,'')[RxRight4],  
STUFF((SELECT ', ' + left(disp.disp_drug_name,2)  
FROM cprx_disp disp  
WHERE om.order_id = disp.order_id   
FOR XML PATH('')),1,1,'') [DrugLeft2]  
FROM csom om (nolock)  


join cprx_disp disp (nolock) on om.order_id = disp.order_id  
join cprx rx (nolock) on disp.rx_id = rx.rx_id and  (rx.refills_left  
is not NULL AND Not(rx.refills_left between -0.1 and 0.1))  
join cppat pat (nolock) on rx.pat_id = pat.pat_id  
join cppat_ins patins (nolock) on pat.pat_id = patins.pat_id  
WHERE (pat.email <> 'none' and pat.email <> ' ' and pat.email IS NOT  
NULL)  
AND ISNULL(pat.status_cn, 0) IN (0, 1, 2)  
AND ISNULL(pat.pat_status_cn, 1) IN (0, 1)  
and ((dispense_date + (disp_days_supply*.75)) BETWEEN @StartDate and  
@Enddate  
and (rx.refills_left is not NULL AND Not(rx.refills_left between -0.1  
and 0.1))  
AND  rx.store_id in (1)  
AND IsNull(rx.manual_add_yn,0) = 0  
AND rx.script_status_cn = 0  
AND disp.rxdisp_id = rx.last_rxdisp_id)  
Group by Pat.email, Pat.fname, disp.rx_id,disp.rxdisp_id, om.order_id  
Order by INS1,ADDR

我包括了我的加入，以防万一这可能是我的问题。因此，我需要将 >[RxRight4] 值全部放在一行中，由 INS1 和 ADDR > 值汇总。它对 [DrugLeft2] 很好，但对 [RxRight4] 则不然。

因此，例如，我需要结果是这样的：

ADDR              INS1        RxRight4               DrugLeft2
Joe@gmailcom      ABC Group   1234, 4321, 3124       AB, BC, CD

But, what I get is this:

ADDR              INS1        RxRight4               DrugLeft2
Joe@gmail.com     ABC Group   1234                   AB, BC, CD
Joe@gmail.com     ABC Group   4321                   AB, BC, CD
Joe@gmail.com     ABC Group   3124                   AB, BC, CD

标签： sql-server

解决方案

马克，我用@table 变量模拟了您的数据，请注意我不确定您真正拥有什么数据，但我相信这足以解决您的查询。

首先，我将 STUFF 函数中的嵌套 SELECT 与主 SELECT 中的 JOIN 分开。现在，这个嵌套查询将在每个结果行中单独执行。请注意，主 JOIN 中的表具有新的别名 disp2 和 rx2。

其次，在 [RxRight4] 列的 STUFF 函数中的嵌套 SELECT 中，我添加了“JOIN @cprx_disp disp ON om.order_id = disp.order_id”以通过独立于主 SELECT 中的 disp2 表的 disp 表将 om 表与 cprx rx 连接。

第三，我评论了一些我不知道哪些数据满足它的 WHERE 子句：

    -- AND LEN(rx.script_no) = 7
    --AND ((dispense_date + (disp_days_supply*.75)) BETWEEN @StartDate AND @Enddate 
    --   AND (rx.refills_left IS NOT NULL AND NOT(rx.refills_left between -0.1 AND 0.1)) 
    --   AND  rx.store_id in (1)  AND ISNULL(rx.manual_add_yn,0) = 0  AND rx.script_status_cn = 0 AND disp.rxdisp_id = rx.last_rxdisp_id)

示例代码现在如下所示：

  DECLARE @cppat TABLE (id int, pat_id int, email varchar(20), fname varchar(20), status_cn int, pat_status_cn int );
  DECLARE @cppat_ins TABLE (id int, pat_id int, status_cn int);
  DECLARE @cprx TABLE (id int,rx_id int, last_rxdisp_id int,pat_id int, script_no varchar(10), refills_left int);
  DECLARE @cprx_disp TABLE (id int, rx_id int, order_id int, rxdisp_id int, disp_drug_name varchar(20));
  DECLARE @csom TABLE (order_id int);



  INSERT INTO @cppat (id, pat_id, email, fname, status_cn, pat_status_cn)
  VALUES 
        (1,1,'Joe.gmail.com','ABC GROUP',1,1);

  INSERT INTO @cppat_ins (id, pat_id, status_cn)
  VALUES 
        (1,1,1);

  INSERT INTO @cprx (id, rx_id, last_rxdisp_id, pat_id, script_no, refills_left)
  VALUES 
         (1,1,1,1,'1234',1)
        ,(1,2,1,1,'4321',1)
        ,(1,3,1,1,'3124',1);

 INSERT INTO @cprx_disp (id, rx_id, order_id, rxdisp_id, disp_drug_name)
  VALUES 
          (1,1,1,1,'AB')
         ,(1,2,1,1,'BC')
         ,(1,3,1,1,'CD');

 INSERT INTO @csom (order_id)
 VALUES
        (1);

  DECLARE @StartDate DateTime, @EndDate DateTime;
  SET @StartDate = GETDATE() - 1;
  SET @EndDate = GETDATE();

  SELECT DISTINCT  
      pat.email as ADDR,  
      pat.fname as INS1,  
      STUFF((SELECT ', ' + right(rx.script_no,4)  
             FROM @cprx rx  
             JOIN @cprx_disp disp ON om.order_id = disp.order_id         
             WHERE disp.rx_id = rx.rx_id  
               AND disp.rxdisp_id = rx.last_rxdisp_id  
             AND (rx.refills_left IS NOT NULL AND NOT(rx.refills_left BETWEEN -0.1 AND 0.1))  
             --AND LEN(rx.script_no) = 7  
             FOR XML PATH('')),1,1,'') [RxRight4],  

      STUFF((SELECT ', ' + left(disp.disp_drug_name,2)  
             FROM @cprx_disp disp  
             WHERE om.order_id = disp.order_id   
             FOR XML PATH('')),1,1,'') [DrugLeft2]  
  FROM @csom om 

  JOIN @cprx_disp disp2 ON om.order_id = disp2.order_id  
  JOIN @cprx rx2 ON disp2.rx_id = rx2.rx_id AND (rx2.refills_left IS NOT NULL AND NOT(rx2.refills_left between -0.1 and 0.1))  
  JOIN @cppat pat ON rx2.pat_id = pat.pat_id  
  JOIN @cppat_ins patins ON pat.pat_id = patins.pat_id 

  WHERE (pat.email <> 'none' AND pat.email <> ' ' AND pat.email IS NOT NULL)  
        AND ISNULL(pat.status_cn, 0) IN (0, 1, 2)  
        AND ISNULL(pat.pat_status_cn, 1) IN (0, 1)  
        --AND ((dispense_date + (disp_days_supply*.75)) BETWEEN @StartDate AND @Enddate 
        --   AND (rx.refills_left IS NOT NULL AND NOT(rx.refills_left between -0.1 AND 0.1)) 
        --   AND  rx.store_id in (1)  AND ISNULL(rx.manual_add_yn,0) = 0  AND rx.script_status_cn = 0 AND disp.rxdisp_id = rx.last_rxdisp_id)

  GROUP BY Pat.email, Pat.fname, disp2.rx_id,disp2.rxdisp_id, om.order_id  
  ORDER BY INS1,ADDR

查询的结果是：

   ADDR                 INS1                 RxRight4            DrugLeft2
   -------------------- -------------------- ------------------- --------------------
   Joe.gmail.com        ABC GROUP             1234, 4321, 3124   AB, BC, CD

推荐阅读