15,567,447 members
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 7:28am

## Solution 1

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

## Solution 2

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

experimentalzee 12-Jan-21 6:33am
Thank you for the help! :)
MarcusCole6833 12-Jan-21 9:34am
np