Question

在我的SQL中,如何做到这一点:

数据:

Item, class, x, y, z, A, B, C
1,     new,  1, 0, 0, 1, 1, 0
2,     red,  1, 1, 0, 0, 1, 1
3,     new,  0, 0, 1, 1, 0, 1

(Data is also in form 1, new, X, AB or 2, Red, XY,BC/code> 如果这是比较容易的出发点......



需要:

1, new, X, A
1, new, X, B
2, red, X, B
2, red, X, C
2, red, Y, B
2, red, Y, C
3, new, Z, A
3, new, Z, C


任何帮助都会受到高度赞赏。 感谢。

Answer 1

“xyzABC.......”一栏:

SELECT item,class, x , A  FROM `test_pivot` WHERE x=1 AND a=1 UNION
SELECT item,class, x , B  FROM `test_pivot` WHERE x=1 AND b=1 UNION
SELECT item,class, x , C  FROM `test_pivot` WHERE x=1 AND c=1 UNION
SELECT item,class, y , A  FROM `test_pivot` WHERE y=1 AND a=1 UNION
SELECT item,class, y , B  FROM `test_pivot` WHERE y=1 AND b=1 UNION
SELECT item,class, y , C  FROM `test_pivot` WHERE y=1 AND c=1 UNION
SELECT item,class, z , A  FROM `test_pivot` WHERE z=1 AND a=1 UNION
SELECT item,class, z , B  FROM `test_pivot` WHERE z=1 AND b=1 UNION
SELECT item,class, z , C  FROM `test_pivot` WHERE z=1 AND c=1

Answer 2

采用封套的<代码> 例说明,如:

 select t.item, t.class, 
 Case when t.X = 1 then  X  else
      case when t.Y = 1 then  Y  else
           case when t.Z = 1 then  Z  end
      end
 end ,
 Case when t.A = 1 then  A  else 
      case when t.B = 1 then  B  else
           case when t.C = 1 then  C  end
      end
 end
 from YourTableName t

友情链接