I've got a table Schedule
Id | unserId | Sunday | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday |
---|---|---|---|---|---|---|---|---|
0 | 10 | 0 | 1 | 1 | 1 | 1 | 0 | 0 |
1 | 20 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
I need to insert lines from one row in table Schedule into seven rows in table Attending, So the result would looks like this:
Id | userId | Day | Status |
---|---|---|---|
1 | 10 | 0 | 0 |
2 | 10 | 1 | 1 |
3 | 10 | 2 | 1 |
4 | 10 | 3 | 1 |
5 | 10 | 4 | 0 |
6 | 10 | 6 | 0 |
7 | 10 | 5 | 0 |
8 | 20 | 0 | 1 |
9 | 20 | 1 | 0 |
10 | 20 | 2 | 0 |
To solve this, I think of using Cursor:
I would use two cursors, one for each row in the table Schedule and the other for the 7 days of each row!
Is it the right way of doing it, or there is a better way of doing it?
PIVOT
or really more soUNPIVOT
if you're trying to go from the structure ofSchedule
to the structure ofAttending
.