I used this code to recreate your scenario (including it here in case you have a query on my solution!)
declare @tab table (ResultID int, Test_UK int, Test_US int, Test_UK_Score int, Test_US_Score int)
insert into @tab (ResultID,Test_UK,Test_US,Test_UK_Score,Test_US_Score) values
( 1 , 1 , 2 , 48 , 11),
( 2 , 4 , 1 , 21 , 24),
( 3 , 3 , 1 , 55 , 71),
( 4 , 5 , 6 , 18 , 78),
( 5 , 7 , 4 , 19 , 49),
( 6 , 1 , 3 , 23 , 69),
( 7 , 5 , 2 , 98 , 35),
( 8 , 6 , 7 , 41 , 47)
Now, I was able to get your results using a table variable (similar to my test data) but you would probably class that as a "temporary table". I was also able to get your results using a Common Table Expression (CTE) - but you would probably class that as a "sub-query" even though it isn't really.
There is another way you can do that using UNPIVOT
]. The complication arises here because you need to UNPIVOT on two
columns to get the results you need.
There is a worked example here - SQL Banana: SQL UNPIVOT on Multiple Columns
I ended up with
select TestId, MAX(Score) AS MaxScore, MIN(Score) AS MinScore
SELECT ResultId, Test_UK, Test_US, Test_UK_Score,Test_US_Score
TestId FOR T IN (Test_UK, Test_US)
Score for S IN (Test_UK_Score, Test_US_Score)
WHERE SUBSTRING(S, 6,2) = SUBSTRING(T, 6,2)
GROUP BY TestID
Points to note:
- This may appear to look like a "sub-query" but that is just the syntax for an
- You don't have to "nest" the
s - you just chain them together
- Notice the "made up" columns
. This is where the real power of matching up Test_UK with Test_UK_Score happens - try taking out that
clause and see what happens.
EDIT: Are you aware that your table design is not very good - it needs to be normalised. One way of doing that would be to use the UNION construct mentioned by @mohibur-rashid to insert normalised data into a new (permanent) table e.g.
declare @tab2 table (ResultID int, TestId int, Score int, TestCountry varchar(3))
insert into @tab2
select ResultId, Test_UK as TestId, Test_UK_Score as Score, 'UK' as TestCountry
select ResultId, Test_US, Test_US_Score, 'US' as TestCountry
Note the new column to indicate the country the test was taken in.
Why is this better? Well getting the results you wanted is trivial
select TestId, MAX(score) AS MaxScore, MIN(Score) as MinScore
GROUP BY TestId
It's also extensible. If you started taking the tests in Australia too then you would have to go to the trouble of changing your original table AND changing your query. But with the normalised version you just have to have a new code for TestCountry. e.g.
insert into @tab2 (ResultID, TestId, Score, TestCountry) values
(9, 8, 41, 'AU'),
(10, 8, 32, 'US'),
(11, 8, 42, 'UK')
And no changes to the query at all.
The UNPIVOT construct is often used to normalise tables "on the fly"