CODEWITHSUNDEEP
mysqlsqlunion
Larry Burns
Larry Burns

Reputation: 5

Turn Columns into Rows SQL

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.

Upvotes: 0

Views: 85

Answers (2)

BrianAtkins
BrianAtkins

Reputation: 1349

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

Upvotes: 1

mucio
mucio

Reputation: 7119

I think there is no other way than using UNION.

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

Upvotes: 0

Related Questions