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?

Improve this question
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. Commented Sep 29, 2022 at 18:45

1 Answer 1

Reset to default
-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;
Improve this answer
1
  • This query clearly answer of the question. Test it. Why downvoting? Commented Dec 9, 2022 at 8:08

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.