How do I split a single row into multiple rows based on columns?

Question

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 11-Jan-21 0:35am

experimentalzee

Updated 11-Jan-21 6:28am

Add a Solution

2 solutions

Add a Solution

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

Maciej Los · Accepted Answer · 2021-01-11T00:40:00

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[^]

MarcusCole6833 · Accepted Answer · 2021-01-11T06:28:00

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

How do I split a single row into multiple rows based on columns?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

How do I split a single row into multiple rows based on columns?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us