sql - 如何将具有相同 id 的多条记录的表重塑为每个 id 一条记录的表而不会丢失信息？

Question

基本上，我想将this(Initial)转换为this(Final)。换句话说，我想

• “挤压”初始表，使其每个 id 只有一条记录

• “扩大”初始表，这样我就不会丢失任何信息：为初始表中的源和列的每个可能组合创建一个不同的列（创建 c1_A、c1_B、...）。

我可以在 Python（可能是 Pandas）中将初始表作为 csv 使用，并手动对Initial和Final表之间的映射进行硬编码。但是，我觉得这个解决方案一点也不优雅，而且我对 sql / sas 解决方案更感兴趣。有没有办法做到这一点？

编辑：我要改变什么

+----+--------+------+-----+------+
| ID | source |  c1  | c2  |  c3  |
+----+--------+------+-----+------+
|  1 | A      |  432 |  56 |    1 |
|  1 | B      |   53 |   3 |   73 |
|  1 | C      |    7 | 342 |   83 |
|  1 | D      |  543 |  43 |   73 |
|  2 | A      |    8 | 882 |   39 |
|  2 | B      |    5 |  54 |   46 |
|  2 | C      |    8 |   3 | 2226 |
|  2 | D      |   87 |   2 |   45 |
|  3 | A      |   93 | 143 |   45 |
|  3 | B      | 1023 |  72 |    8 |
|  3 | C      |    3 |   3 |  704 |
|  4 | A      |    2 |   5 |    0 |
|  4 | B      |   78 | 888 |    2 |
|  4 | C      |   87 |  23 |   34 |
|  4 | D      |  112 |   7 |  712 |
+----+--------+------+-----+------+

进入

+----+------+------+------+------+------+------+------+------+------+------+------+------+
| ID | c1_A | c1_B | c1_C | c1_D | c2_A | c2_B | c2_C | c2_D | c3_A | c3_B | c3_C | c3_D |
+----+------+------+------+------+------+------+------+------+------+------+------+------+
|  1 |  432 |   53 |    7 |  543 |   56 |    3 |  342 |   43 |    1 |   73 |   83 |   73 |
|  2 |    8 |    5 |    8 |   87 |  882 |   54 |    3 |    2 |   39 |   46 | 2226 |   45 |
|  3 |   93 | 1023 |    3 |      |  143 |   72 |    3 |      |   45 |    8 |  704 |      |
|  4 |    2 |   78 |   87 |  112 |    5 |  888 |   23 |    7 |    0 |    2 |   34 |  712 |
+----+------+------+------+------+------+------+------+------+------+------+------+------+

标签： sqlpandassas

Answer 1

放弃希望……？

data want;
input 
  ID   source $  c1    c2     c3;datalines;
   1   A         432    56      1  
   1   B          53     3     73  
   1   C           7   342     83  
   1   D         543    43     73  
   2   A           8   882     39  
   2   B           5    54     46  
   2   C           8     3   2226  
   2   D          87     2     45  
   3   A          93   143     45  
   3   B        1023    72      8  
   3   C           3     3    704  
   4   A           2     5      0  
   4   B          78   888      2  
   4   C          87    23     34  
   4   D         112     7    712  
;

* one to grow you oh data;
proc transpose data=want out=stage1;
  by id source;
  var c1-c3;
run;

* and one to shrink;
proc transpose data=stage1 out=want(drop=_name_) delim=_;
  by id;
  id _name_ source;
run;