0

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.

Larry Burns
  • 5
  • 2

2 Answers2

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
BrianAtkins
  • 1,279
  • 9
  • 17
0

I think there is no other way than using UNION.

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

Community
  • 1
  • 1
mucio
  • 7,014
  • 1
  • 21
  • 33