none
T-SQL Pivot Query - How to format output of row to column sql query

    Question

  • I have the Following Data in a table (row) format based on [Test Parameter Name].

    [Serial Number]		[Model]	[Recv_Date]	[Test Date]	[Test Parameter Name]	[Test Value]	[Test Result]	[Tester Name_ID]	[Error_Defect Code]
    100811000023	M9001	3/20/13 14:18	3/22/13 0:42	Test_1				0			FAIL			dev04_9			DC01
    100811000023	M9001	3/20/13 14:18	3/22/13 0:42	Test_2				100			PASS			dev04_9			DC01
    100811000023	M9001	3/20/13 14:18	3/22/13 0:42	Test_3				0			FAIL			dev04_9			DC02
    100811000023	M9001	3/20/13 14:18	3/22/13 0:42	Test_4				NULL		SKIPPED		dev04_9			DC02
    100811000023	M9001	3/20/13 14:18	3/22/13 0:42	Test_5				120			PASS			dev04_9			DC03
    100899000345	M9001	3/20/13 23:28	3/21/13 15:55	Test_1				99			PASS			dev09_5			DC01
    100899000345	M9001	3/20/13 23:28	3/21/13 15:55	Test_2				101			PASS			dev09_5			DC01
    100899000345	M9001	3/20/13 23:28	3/21/13 15:55	Test_3				103			PASS			dev09_5			DC02
    100899000345	M9001	3/20/13 23:28	3/21/13 15:55	Test_4				100			PASS			dev09_5			DC02
    100899000345	M9001	3/20/13 23:28	3/21/13 15:55	Test_5				119			PASS			dev09_5			DC03
    100811000023	M9001	3/20/13 14:18	3/23/13 13:15	Test_1				0			FAIL			dev04_9			DC01
    100811000023	M9001	3/20/13 14:18	3/23/13 13:15	Test_2				100			PASS			dev04_9			DC01
    100811000023	M9001	3/20/13 14:18	3/23/13 13:15	Test_3				0			FAIL			dev04_9			DC02
    100811000023	M9001	3/20/13 14:18	3/23/13 13:15	Test_4				NULL		SKIPPED		dev04_9			DC02
    100811000023	M9001	3/20/13 14:18	3/23/13 13:15	Test_5				120			PASS			dev04_9			DC03

    I need the output to be a single record by [Serial Number] based on max [Test Date] (a unit could run multiple times). I then need to look at all the [Test Parameter Name]'s test results to see if they PASSED or FAILED. If multiple failures I need to display the [Error_Defect Code] by priority. Note: multiple tests are grouped into an [Error_Defect Code] and in the example data, the reporting priority of any test that fail will be: [Error_Defect Code]= "DC03", then "DC02", "DC01".... If all tests PASS, display "DC00" for the [Error_Defect Code]. I would also like to pivot the [Test Parameter Name] to show [Test Value].

    The data example only shows 5 different [Test Parameter Name] values. in reality, my actual data has 100 different [Test Parameter Name] values with 15 different [Error_Defect Code]'s.

    Hear is how I desire the output to look:

    [Serial Number]		[Model]	[Recv_Date]	[Test Date]	[Test Result]	[Error_Defect Code]	[Tester Name_ID]	[Test_1]	[Test_2]	[Test_3]	[Test_4]	[Test_5]...
    100811000023	M9001	3/20/13 14:18	3/23/13 13:15	FAIL			DC02			dev04_9            	0		100		0		NULL	120
    100899000345	M9001	3/20/13 23:28	3/21/13 15:55	PASS			DC00			dev09_5			99		101		103		100		119









    • Edited by j1124g Friday, November 15, 2013 10:37 PM
    Friday, November 15, 2013 10:08 PM

Answers

All replies