Manipulating SPC Data

Ignition
1

So what I am trying to do is take the SPC Data and pivot it. here is my query so far.

SELECT DISTINCT sample.DefUUID,sample.SampleUUID,sample.Location, sample.ProductCode, sample.RefNo,
(
SELECT TOP(1) FactorValue
FROM QUAL_AddlFactor as addl1
WHERE addl1.SampleUUID = data.SampleUUID AND addl1.FactorName = 'Work Order' 
) AS WorkOrder,
(
SELECT TOP(1) FactorValue
FROM QUAL_AddlFactor as addl
WHERE addl.SampleUUID = data.SampleUUID AND addl.FactorName = 'Batch' 
) AS [Batch],
(
SELECT TOP(1) FactorValue
FROM QUAL_AddlFactor as addl
WHERE SampleUUID = data.SampleUUID AND FactorName = 'Roll' 
) AS [Roll],

data.MeasNo,
(
SELECT TOP(1) AttrValue
FROM QUAL_SampleData as iData
WHERE iData.SampleUUID = data.SampleUUID AND iData.SampleUUID = sample.SampleUUID AND iData.MeasNo = data.MeasNo AND AttrName = 'Cavity'
) as [Cavity],
(
SELECT TOP(1) AttrValue
FROM QUAL_SampleData as iData
WHERE iData.SampleUUID = data.SampleUUID AND iData.SampleUUID = sample.SampleUUID AND iData.MeasNo = data.MeasNo AND AttrName = 'Ref. Plate Diam. (in)'
) as [Ref Plate],
(
SELECT TOP(1) AttrValue
FROM QUAL_SampleData as iData
WHERE iData.SampleUUID = data.SampleUUID AND iData.SampleUUID = sample.SampleUUID AND iData.MeasNo = data.MeasNo AND AttrName = 'Diam. Difference (in)'
) as [Diam Difference],
(
SELECT TOP(1) AttrValue
FROM QUAL_SampleData as iData
WHERE iData.SampleUUID = data.SampleUUID AND iData.SampleUUID = sample.SampleUUID AND iData.MeasNo = data.MeasNo AND AttrName = 'Skirt min (in)'
) as [Skirt Min],
(
SELECT TOP(1) AttrValue
FROM QUAL_SampleData as iData
WHERE iData.SampleUUID = data.SampleUUID AND iData.SampleUUID = sample.SampleUUID AND iData.MeasNo = data.MeasNo AND AttrName = 'Skirt max (in)'
) as [Skirt Max]

FROM QUAL_SampleData as data
JOIN QUAL_Sample as sample
ON data.SampleUUID = sample.SampleUUID

ORDER BY Location, WorkOrder DESC, Batch ASC, Roll ASC

And this acheives ALMOST what i want, it doesnt do it accurately, and it isnt broad at all. I am assuming i will have to do this in script, or some REALLY fancy SQL, can i get any advice or hints or directions on what to do here?

2

It looks like you are using SQLServer, right? MSSQL has a PIVOT function that might give you the format you want. You can look up more about it (search for “tsql pivot”) and here’s a quick reference to the pivot command.

Alternatively, you could do this in a propertyChange event script, but which is better depends on how familiar you are with scripting vs. SQL queries.