Normalize Data

Hi,
I have a table like this:






Pk_QuestionID

Question

Choice1

Choice2

Choice3

Choice4

correctChoice


1

XYZ?

a

b

C

d

3

This has to be transferred to a more normalized schema like this







Pk_QuestionID

Question


1

XYZ?

And







Pk_OptionID

Fk_QuestionID

Choice_Description

isCorrect


1

1

a

0


2

1

b

0


3

1

c

1


4

1

d

0


We can use either the unpivot-merge or a SQL Union ALL statement to achieve most of this requirement. I am unable to figure out how to transfer the €˜correctChoice€™ column. Is there a way to achieve this?

Thanks