I'm sorry if the title is confusing. I have a very large source table that is structured somewhat like this
From Mile | To Mile |
| |From Weight| 1000 | 1100 | 1200 | 1300... | 22500 | 23000
| |To Weight | 1099 | 1199 | 1299 | 1499... | 22999 | 23499
==========================================================================
1 | 20 | | 1500 | 1505 | 1489 | 1854... | 3400 | 2990
------------------------------------------------------------------------
21 | 40 | | 1400 | 1705 | 491 | 5581... | 1600 | 2989
------------------------------------------------------------------------
41 | 60 | | 1800 | 1635 | 1982 | 1888... | 3200 | 3480
------------------------------------------------------------------------
Where one can pick a certain milage, e.g. 23, and a certain weight, e.g. 1250, and get a "rate" - in this case, 491.
From Mile | To Mile |
| |From Weight| | | 1200 | | |
| |To Weight | | | 1299 | | |
==========================================================================
| | | | | | ... | |
------------------------------------------------------------------------
21 | 40 | | | | 491 | ... | |
------------------------------------------------------------------------
| | | | | | ... | |
------------------------------------------------------------------------
I would want to insert each rate with their corresponding weight and mile ranges.
Id | Mile_To | Mile_From | Weight_To | Weight_From | Rate
===========================================================
0 | 1 | 20 | 1000 | 1099 | 1500
-----------------------------------------------------------
1 | 1 | 20 | 1100 | 1199 | 1505
-----------------------------------------------------------
2 | 1 | 20 | 1200 | 1299 | 1389
-----------------------------------------------------------
3 | 1 | 20 | 1300 | 1499 | 1854
-----------------------------------------------------------
...
-----------------------------------------------------------
40 | 1 | 20 | 22500 | 22999 | 3400
-----------------------------------------------------------
41 | 1 | 20 | 23000 | 23499 | 2990
-----------------------------------------------------------
42 | 21 | 40 | 1000 | 1199 | 1400
What is the best way to step through the source table and insert the data as formatted above? There around hundreds of rows and columns, so naming each one would be similar to manually entering the data.
Here is a SQLFiddle with an example of the source table: http://sqlfiddle.com/#!18/48777a/1
You can use cross apply to unpivot this dataset:
select
s.from_miles,
s.to_miles,
x.*
from source s
cross apply (values
(1000, 1099, s.[1000_to_1099]),
(1100, 1199, s.[1100_to_1199]),
(1200, 1299, s.[1200_to_1299]),
(1300, 1399, s.[1300_to_1399]),
(1400, 1499, s.[1400_to_1499])
) as x(weight_from, weigh_to, rate)
You can easily turn this to an insert statement:
insert into newtable (from_moles, to_moles, weight_from, weight_to, rate)
select ... -- above query