I've been looking everywhere for a tutorial, but nothing quite fits what I need, so I'm almost guessing what I want isn't possible.
I have a table that contains an ID for a batch of product, followed by several test results (i.e. BatchNumber, Type, TestResultOne, TestResultTwo, etc...). There are thirty tests in total, for each batch.
I am trying to create a select statement or a view that displays the batch number, the ordinal_position of the test column (i.e. 3 for TestResultOne) and the value of that result. So when I run the select statement in my database, I receive one row, but when I run my select/view, I need 29 rows (one for each of the tests conducted on the product).
Is this possible? If so: is there a tutorial out there showing me how to do it, and if not, how the heck do I get around this issue?
For example, my table looks like so:
BatchID | Type | TestOne | TestTwo | TestThree | Test Four
----------------------------------------------------------
00001 | A | 1.2 | 0 | 16 | PASS
00002 | A | 1.3 | 1 | 15 | FAIL
and I am hoping to get:
BatchID | Test | Result
-----------------------
00001 | 2 | 1.2
00001 | 3 | 0
00001 | 4 | 16
00001 | 5 | PASS
It seems that what you need to do is UNPIVOT your table. Depending of the version of SQL Server one could use the UNPIVOT statement. I prefer UNIONing the results instead of using the UNPIVOT syntax. Something like the following should work for you.
DECLARE @TestResults TABLE (
BatchID Int,
TestType CHAR(1),
TestOne SMALLMONEY,
TestTwo SMALLMONEY,
TestThree SMALLMONEY,
TestFour SMALLMONEY
)
INSERT INTO @TestResults
SELECT 1, 'A', 1.2, 0, 16, 8.2 UNION
SELECT 2, 'A', 1.3, 1, 15, 7.4
SELECT BatchID, TestType, TestOne, TestTwo, TestThree, TestFour FROM @TestResults
This will return your current results.
BatchID TestType TestOne TestTwo TestThree TestFour
----------- -------- ----------- ----------- ----------- -----------
1 A 1.20 0.00 16.00 8.20
2 A 1.30 1.00 15.00 7.40
Try the following query to UNPIVOT your data.
SELECT BatchID, 1 AS Test, TestOne AS Result FROM @TestResults UNION ALL
SELECT BatchID, 2 AS Test, TestTwo FROM @TestResults UNION ALL
SELECT BatchID, 3 AS Test, TestThree FROM @TestResults UNION ALL
SELECT BatchID, 4 AS Test, TestFour FROM @TestResults
ORDER BY BatchID, Test
This should return the desired results.
BatchID Test Result
----------- ----------- -----------
1 1 1.20
1 2 0.00
1 3 16.00
1 4 8.20
2 1 1.30
2 2 1.00
2 3 15.00
2 4 7.40