根据SQL或R中的某些条件复制行 [英] Duplicate the rows based on some criteria in SQL or R

问题描述

我用R生成玩具套装

data.frame(name = c("Tom", "Shane", "Daniel", "Akira", "Jack", "Zoe"), c1 = c(1,2,3,0,5,0), c2 = c(0, 3, 5, 0,4,0), c3 = c(0, 0,1,0,0,3), c4=c(0,0,0,1,0,0))

显示在下面:

我只关心列c1, c2, c3, c4，如果特定行有多个值(大于0)，则需要复制行以确保只有一个值(大于0). ，然后删除原始行.

I only care about the columns c1, c2, c3, c4, and if a specific row has more than one value, which is greater than 0. we need to duplicate rows to make sure that there are only one value, which is greater than 0, and then remove the original row.

例如，第二行有两个大于0的值(c1:2，c2:3)，那么我们必须将该行复制为两个，看起来像这样

For instance, the second row has two values are greater than 0 (c1: 2, c2: 3), then we have to duplicate that row to two, which looks like this

Shane 2 0 0 0

Shane 0 3 0 0

我正在尝试构建一个SQL查询来捕获此问题.但是，我不确定是否有任何SQL函数可以在不先查看结果的情况下检测特定行中的多个非零值.无论如何，如果存在任何神奇的SQL函数，最终结果应该看起来像这样:

I am trying to build a SQL query to capture this. However, I am not sure if there is any SQL function can detect multiple non-zero values in a specific row without looking at the result first. Anyway the final result should look like this, if there any magical SQL functions exist:

我还考虑使用R来完成它.我知道的唯一可以复制行的R函数是do.call()函数，然后将其与rbind()函数合并.但是，它不适用于我的情况.有人可以给我提示吗?非常感谢:)

I also think about to use R to accomplish it. The only R function I know can duplicate rows is do.call() function, then combine it with rbind() function. However, it is not working for my case. Could you someone give me any hints? Many Thanks :)

推荐答案

也许是使用CROSS APPLY

示例

Select A.Name
      ,B.*
 From  YourTable A
 Cross Apply ( values (C1,0,0,0)
                     ,(0,C2,0,0)
                     ,(0,0,C3,0)
                     ,(0,0,0,C4)
             ) B (C1,C2,C3,C4)
 Where B.C1+B.C2+B.C3+B.C4<>0

返回

这篇关于根据SQL或R中的某些条件复制行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！