PLSQL双拆分使用分隔符，然后以某种方式转置？

Question

这就是我认识到理论和实践之间的区别的地方。因为虽然理论上我可以想象它应该是什么样子的，但我不能在我的生活中真正想出如何去做它。我有成千上万的观察结果如下：

>+--------+-------------------------------+--+
>|   ID   |             CALLS             |  |
>+--------+-------------------------------+--+
>| 162743 | BAD DVR-3|NO PIC-1            |  |
>|  64747 | NO PIC-1|BOX HIT-4|PPV DROP-1 |  |
>+--------+-------------------------------+--+

最终结果应该是这样的：

+--------+---------+--------+---------+----------+--+
|   ID   | BAD DVR | NO PIC | BOX HIT | PPV DROP |  |
+--------+---------+--------+---------+----------+--+
| 162743 |       3 |      1 |       0 |        0 |  |
|  64747 |       0 |      1 |       4 |        1 |  |
+--------+---------+--------+---------+----------+--+

我在SAS中使用passthru，所以如果我需要进行转置，我也可以始终使用proc。但说到这一点，我实在无法理解。我知道我可能需要创建一个类似于这里提出的问题的函数：T-SQL: Opposite to string concatenation - how to split string into multiple records

有什么想法吗？

Answer 1

您是否有任何参考资料来描述“调用”列中那些管道分隔值的所有可能值？还是你已经知道你需要保留哪些特定的价值观，并且可以忽略其他的价值观？

如果是这样，您可以在一个数据步骤中处理整个事件；下面是一个示例：

data have;
   input @1 ID 6. @9 CALLS $50.;
   datalines;
162743  BAD DVR-3|NO PIC-1            
 64747  NO PIC-1|BOX HIT-4|PPV DROP-1 
run;

data want;
   set have; /* point to your Oracle source here */ 
   length field $50;
   idx = 1;
   BAD_DVR = 0;
   NO_PIC = 0;
   BOX_HIT = 0;
   PPV_DROP = 0;
   do i=1 to 5 while(idx ne 0);
      field = scan(calls,idx,'|');
      if field = ' ' then idx=0;
      else do;
         if      field =: 'BAD DVR'  then BAD_DVR  = input(substr(field,9),8.);
         else if field =: 'NO PIC'   then NO_PIC   = input(substr(field,8),8.);
         else if field =: 'BOX HIT'  then BOX_HIT  = input(substr(field,9),8.);
         else if field =: 'PPV DROP' then PPV_DROP = input(substr(field,10),8.);
         idx + 1;
         end;
      end;
   output;
   keep ID BAD_DVR NO_PIC BOX_HIT PPV_DROP;
run;

扫描函数通过令牌遍历CALLS列；":=“操作符是”with"，只有两个参数的SUBSTR函数查找输入函数要读取的连字符后面的字符。

当然，我对你的源数据做了一些假设，但你明白了。

Answer 2

我至少可以想到两种方法来实现这一点: 1.将整个数据从SQL读入SAS。然后使用DATA STEP操作数据，即转换两列中的数据：

>+--------+-------------------------------+--+
>|   ID   |             CALLS             |  |
>+--------+-------------------------------+--+
>| 162743 | BAD DVR-3|NO PIC-1            |  |
>|  64747 | NO PIC-1|BOX HIT-4|PPV DROP-1 |  |
>+--------+-------------------------------+--+

像这样的东西：result of DATA STEP manipulation:

ID      CALLS       COUNT
162743  BAD_DVR       3
162743  NO_PIC        1
64747   NO_PIC        1
64747   BOX_HIT       4
64747   PPV_DROP      1

从那时起，将上述数据集传递到PROC TRANSPOSE以获得如下表将是一个简单的事情：

+--------+---------+--------+---------+----------+--+
|   ID   | BAD DVR | NO PIC | BOX HIT | PPV DROP |  |
+--------+---------+--------+---------+----------+--+
| 162743 |       3 |      1 |       0 |        0 |  |
|  64747 |       0 |      1 |       4 |        1 |  |
+--------+---------+--------+---------+----------+--+

1. 如果您想要在传递SQL中完成所有的操作，那么这应该很容易，如果没有的话。像{坏的DVR，没有PIC，盒子命中等等.}是小的。

代码将如下所示：

SELECT
ID
,CASE WHEN SOME_FUNC_TO_FIND_LOCATION_OF_SUBSTRING(CALLS, 'BAD DVR-')>0 THEN <SOME FUNCTION TO EXTRACT EVERYTHING FROM - TO |> ELSE 0 END AS BAD_DVR__COUNT
,CASE WHEN SOME_FUNC_TO_FIND_LOCATION_OF_SUBSTRING(CALLS, 'NO PIC-')>0 THEN <SOME FUNCTION TO EXTRACT EVERYTHING FROM - TO |> ELSE 0 END AS NO_PIC__COUNT
,<and so on>
FROM YOUR_TABLE

您只需要查看数据库中可用的字符串操作函数，就可以使一切正常工作。