Turn Columns into Rows SQL

Question

I have a set of table of data that looks like this:

[id],[testid],[date],[type],[score1],[score2],[score3],[score4]

I need it to look like this

[id],[testid][date],[type],[score],[scorecode] (score1)
[id],[testid][date],[type],[score],[scorecode] (score2)
[id],[testid][date],[type],[score],[scorecode] (score3)
[id],[testid][date],[type],[score],[scorecode] (score4)

The scorecode is dependent on which score(1-4), so the score1 row would need a scorecode of ACTMATH, score2 would need a different scorecode.

When I orginally built this, I used UNION ALL. However, I just want to make sure there's not a more efficient way of completing this.

BrianAtkins · Accepted Answer · 2015-04-30 21:30:39Z

1

You should break out the Scores into their own table

Which it has a ScoreCode and Score and a link back to the Id of the Test

And then you would do a SQL Join query and easily get your results in that format.

Tests [testid],[date],[type]
Scores [ScoreId],[testid],[score],[scorecode]

SELECT * 
FROM Tests T
JOIN Scores S
ON T.Testid = S.TestId

edited Apr 30, 2015 at 21:30

answered Apr 30, 2015 at 21:25

BrianAtkins

1,3599 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:28:32Z

0

I think there is no other way than using UNION.

You can check these other question: Mysql Convert Column to row (Pivot table)

edited May 23, 2017 at 12:28

CommunityBot

11 silver badge

answered Apr 30, 2015 at 21:25

mucio

7,1271 gold badge23 silver badges36 bronze badges

Collectives™ on Stack Overflow

Turn Columns into Rows SQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related