This is my table structure:

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)
)

Inserting some values:

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 this:

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

CodePudding user response：

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)

sqlfiddle

CodePudding user response：

We can use unpivot, but this only makes one column of values. We can get around this by concatenating the values.
We could also join 2 unpivoted table, but that would be heavier.

SELECT   Studentid, att, [att_val    val]
FROM (   
  SELECT StudentID,
          convert(nvarchar(max),StudentName) StudentName,
          convert(nvarchar(max),Performance)Performance,
          convert(nvarchar(max),class) class,
          convert(nvarchar(max),Section) Section,
          convert(nvarchar(max),concat(left(concat(subject1,'          '),10),':',subjectmark1)) s1,
    convert(nvarchar(max),concat(left(concat(subject2,'          '),10),':',subjectmark2)) s2,
    convert(nvarchar(max),concat(left(concat(subject3,'          '),10),':',subjectmark3)) s3,
    convert(nvarchar(max),concat(left(concat(subject4,'          '),10),':',subjectmark4)) s4,
    convert(nvarchar(max),concat(left(concat(subject5,'          '),10),':',subjectmark5)) s5,
    convert(nvarchar(max),concat(left(concat(subject6,'          '),10),':',subjectmark6)) s6
  FROM StudentMarkList
) sml
UNPIVOT
(   [att_val    val] FOR att IN 
      (StudentName,Performance,class,
    s1,s2,s3,s4,s5,s6)
)   AS VT_Transposee;
GO

Studentid | att         | att_val    val
--------: | :---------- | :-------------
        1 | StudentName | shiva         
        1 | Performance | not bad       
        1 | class       | 10th          
        1 | s1          | science   :58 
        1 | s2          | social    :   
        1 | s3          | english   :70 
        1 | s4          | maths     :   
        1 | s5          | biology   :67 
        1 | s6          |           :58 

db<>fiddle here