I am new to sql server i have the following table structure that contains more than a thousand rows. But for example purposes this is what it would look like
Table Import
+------+---------+------------+------------+------------+------------+------------+
| Name | Code | SocksTotal | GlovesTotal| JeansTotal | ShirtsTotal| shoesTotal |
+------+---------+------------+------------+------------+------------+------------+
| OT | 45612 | 2 | 1 | 0 | 1 | 4 |
| OT | 1234 | 0 | 1 | 0 | 0 | 0 |
| US | 45896| 0 | 0 | 0 | 0 | 0 |
+------+---------+------------+------------+------------+------------+------------+
and a second table called Items follows
+------+---------+
| ID | Item |
+------+---------+
| 1 | socks |
| 2 | Gloves|
| 3 | Jeans |
| 4 | Shirts|
| 5 | shoes |
+------+---------+
from the above tables i need to write a script that would be inserted into a different table called ImportItems_Summary. the expected output is
+------+---------+------------+------------+
| Id | Code | Items_id |Import_total|
+------+---------+------------+------------+
| 1 | 45612 | 1 | 2 |
| 2 | 45612 | 2 | 1 |
| 3 | 45612 | 4 | 1 |
| 4 | 45612 | 5 | 4 |
| 5 | 1234 | 2 | 1 |
+------+---------+------------+------------+
as you can see here that code 45612 now has 4 entries into the ImportItems_summary table where the items is not equal to 0 and the Items_id is linked to the Items table ID column.
How can i achieve the above output?.. I read up and saw a cursor might help but i am not sure how to implement this
One method uses cross apply to unpivot the columns of the unnormalized table to rows, then brings the items table with a join, and finally inserts in the target table:
insert into ImportItems_Summary (code, items_id, import_total)
select im.code, it.items_id, x.import_total
from import im
cross apply (values
('socks', sockstotal),
('gloves', glovestotal),
('jeans', jeanstotal),
('shirts', shirtstotal),
('shoes', shoestotal)
) x(item, import_total)
inner join items it on it.item = x.item