Convert column to multiple rows using SQL Server

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

2 answers

  • answered 2022-05-04 10:20 D-Shih

    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

  • answered 2022-05-04 10:47 Kendle

    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

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum