2

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?

1
  • 1
    Cursors are notorious for being performance bottlenecks and there's almost always a much more efficient solution with not using one. What you're likely looking for here is PIVOT or really more so UNPIVOT if you're trying to go from the structure of Schedule to the structure of Attending.
    – J.D.
    Sep 29, 2022 at 18:45

1 Answer 1

-1

Unpivot data

SELECT userid, weekday, status 
  FROM  (SELECT userid, Sunday, Monday, Tuesday, Wednesday,Thursday,Friday, Saturday
           FROM schedule) scd 
UNPIVOT
(Status FOR weekday IN (Sunday, Monday, Tuesday, Wednesday,Thursday,Friday, Saturday)) AS unpivot;
1
  • This query clearly answer of the question. Test it. Why downvoting? Dec 9, 2022 at 8:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.