Need sql help: For each record in table A (has more columns than table B), insert into Table B

user_8945701 0
Viewed 48

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.

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.

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!

sql-server sql
1 Answers

Just unpivot. I like to do this using 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;
user_11440350
Related