Click here to Skip to main content
15,064,915 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
STUID SUB1 SUB2 SUB3
101 English Science Maths
102 Maths Arts Economics
103 Arts English Social science

Output
101 English
101 Science
101 Maths
102 Maths
102 Arts
102 Economics
103 Arts
103 English
103 Social science

What I have tried:

I have no idea where to begin for this. I'm new to SQL and this problem just stuck in my mind and I was not able to find an answer anywhere.
Posted
Updated 11-Jan-21 6:28am

You need to use UNPIVOT[^]. Follow the link to find out how to achieve that!

SQL
CREATE TABLE PivotedData
(
  STUID int,
  SUB1 varchar(30),
  SUB2 varchar(30),
  SUB3 varchar(30)
);

INSERT INTO PivotedData (STUID, SUB1, SUB2, SUB3)
VALUES(101, 'English', 'Science', 'Maths'),
(102, 'Maths', 'Arts', 'Economics'),
(103, 'Arts', 'English', 'Social science');

SELECT STUID, SubjectID, SubjectName
FROM
(
  SELECT *
  FROM PivotedData
) pvt
UNPIVOT(SubjectName FOR SubjectID IN(SUB1, SUB2, SUB3)) unpvt;


Take a look at db<>fiddle[^]
   
v2
Comments
experimentalzee 12-Jan-21 5:04am
   
Thank you so much! This helped me a lot!
Maciej Los 12-Jan-21 5:12am
   
You're very welcome.
:)
with out using the pivot and some more code added here is one answer with just the two columns

declare @t table
(
  STUID int,
  SUB1 varchar(30),
  SUB2 varchar(30),
  SUB3 varchar(30)
);

declare @n table(stuid int)

INSERT INTO @t (STUID, SUB1, SUB2, SUB3)
VALUES(101, 'English', 'Science', 'Maths'),
(102, 'Maths', 'Arts', 'Economics'),
(103, 'Arts', 'English', 'Social science');

insert into @n
select stuid from @t


--select * from @n

select n.stuid 'Student', t1.sub1 'Subject'
from @n n join @t t1 on t1.stuid = n.stuid
union
select n.stuid, t2.sub2
from @n n join @t t2 on t2.stuid = n.stuid
union
select n.stuid, t3.sub3
from @n n join @t t3 on t3.stuid = n.stuid
   
Comments
experimentalzee 12-Jan-21 6:33am
   
Thank you for the help! :)
MarcusCole6833 12-Jan-21 9:34am
   
np

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900