需要sql帮助:对于表A中的每条记录(比表B有更多的列)，插入到表B中

I know my subject is a little sparse, but for the life of me I cannot figure out how to do this. I could accomplish this in C# but I am getting confused by the SQL syntax. I searched and searched and I can't seem to find what I am looking for probably because I don't understand some of the SQL that I am looking at.

我知道我的主题有点稀疏，但对于我的生命来说，我不知道如何做到这一点。我可以在c#中实现这一点，但是我被SQL语法搞糊涂了。我搜索了又搜索，我似乎找不到我要找的东西，可能是因为我不理解我正在查找的SQL。

TABLE 1
-----------
| CustNo | Catalog1 | Catalog2 | Catalog3 | Catalog4 |
|    1   |    A     |    B     |    C     |   NULL   |
|    2   |    B     |    C     |   NULL   |    D     |
|    3   |    A     |    C     |    E     |    F     |



TABLE 2 (empty)
COLUMNS: CustNo|Catalog

So Basically for each record in Table 1, I want to insert the catalogs into table 2. So the desired output would look like the following.

因此，对于表1中的每条记录，我都想将编目插入到表2中。所以期望的输出是这样的。

TABLE 2
CustNo|Catalog
|  1  |  A
|  1  |  B
|  1  |  C
|  2  |  B
|  2  |  C
|  2  |  D
|  3  |  A
|  3  |  C
|  3  |  E
|  3  |  F

Thank you all for any help!

谢谢大家的帮助!

1 个解决方案

#1

Just unpivot. I like to do this using apply;

透视。我喜欢用apply来做这个;

insert into table2 (CustNo, Catalog)
    select t1.CustNo, v.Catalog
    from table1 t1 cross apply
         (values (t1.Catalog1), (t1.Catalog2), (t1.Catalog3), (t1.Catalog4)
         ) v(catalog)
    where v.Catalog is not null;