create table:
Create table StudesntMarkList(StudentID int , StudentName varchar(100), Performance varchar(100), class varchar(100), Section varchar(100), subject1 varchar(100), subjectmark1 varchar(100), subject2 varchar(100), subjectmark2 varchar(100), subject3 varchar(100), subjectmark3 varchar(100), subject4 varchar(100), subjectmark4 varchar(100), subject5 varchar(100), subjectmark5 varchar(100), subject6 varchar(100), subjectmark6 varchar(100))
Insert Value:
insert into StudesntMarkList values(1, 'shiva', 'not bad','10th','C','science',58,'social','','english',70,'maths','','biology',67,'',58)
I have a table that looks like the one below.
Student ID|Student Name|Performance|class|Section|subject1 |subject mark1|subject2|subject mark2|subject3|subject mark3|subject4|subject mark4|subject5|subject mark5|subject6|subject mark6|
1 |shiva |not bad |10th |c |science |58 |Social | |English |70 |maths | |biology |67 | |50 |
I’m stumped as to what SQL query I’d use to acquire the following result set:
id|att |att val|Val
1 |Student Name |Shiva |
1 |Performance |not bad|
1 |class |10th |
1 |Section |c |
1 |subject1 |science|58
1 |subject2 |Social |
1 |subject3 |English|70
1 |subject4 |maths |
1 |subject5 |biology|67
1 |subject6 | |50
Is this possible?
Kindly help me to solve this, thanks in advance
>Solution :
I would suggest you redesign your table schema and store strategy, I think it might need to do normalization instead of create a lot of columns to store data.
If you want to get to your expect result from your original table, we can try to use CROSS APPLY...VALUE
SELECT StudentID,
v.*
FROM StudesntMarkList
CROSS APPLY (VALUES
('StudentName',StudentName,''),
('Performance',Performance,''),
('class',class,''),
('subject1',subject1,subjectmark1),
('subject2',subject2,subjectmark2),
('subject3',subject3,subjectmark3),
('subject4',subject4,subjectmark4),
('subject5',subject5,subjectmark5),
('subject6',subject6,subjectmark6)
) v(att,attval,Val)