# Manipulating SPC Data

**URL:** <https://forum.inductiveautomation.com/t/manipulating-spc-data/7433>\
**Category:** Ignition\
**Created:** [October 17, 2013, 10:31pm UTC](https://forum.inductiveautomation.com/t/manipulating-spc-data/7433 "2013-10-17T22:31:00Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![stuartd](https://avatars.discourse-cdn.com/v4/letter/s/ecae2f/32.png) [@stuartd](https://forum.inductiveautomation.com/u/stuartd)\
**Post date:** [October 17, 2013, 10:31pm UTC](https://forum.inductiveautomation.com/t/manipulating-spc-data/7433/1 "2013-10-17T22:31:00Z")

</div>

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

```auto
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?

---

<div class="post-metadata">

**Author:** ![Robert.McKenzie](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/robert.mckenzie/32/4134_2.png) [@Robert.McKenzie](https://forum.inductiveautomation.com/u/Robert.McKenzie)\
**Post date:** [December 26, 2013, 6:23pm UTC](https://forum.inductiveautomation.com/t/manipulating-spc-data/7433/2 "2013-12-26T18:23:55Z")

</div>

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](http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx) 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.