# Help with SQL and PIVOT commands

**URL:** <https://forum.inductiveautomation.com/t/help-with-sql-and-pivot-commands/9683>\
**Category:** Ignition\
**Created:** [February 9, 2015, 7:53pm UTC](https://forum.inductiveautomation.com/t/help-with-sql-and-pivot-commands/9683 "2015-02-09T19:53:57Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![tbackus](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tbackus/32/5091_2.png) [@tbackus](https://forum.inductiveautomation.com/u/tbackus)\
**Post date:** [February 9, 2015, 7:53pm UTC](https://forum.inductiveautomation.com/t/help-with-sql-and-pivot-commands/9683/1 "2015-02-09T19:53:57Z")

</div>

Basically I have a table that consists of data Like:

```auto
 Line timestamp AttrName AttrValue
33601 2015-01-01 10:30:00 Bolt 1 0.05
33601 2015-01-01 10:30:00 Bolt 2 0.06
33601 2015-01-01 10:30:00 Bolt 3 0.05
.....
33601 2015-01-01 10:30:00 Bolt 21 0.07
```

I got the Pivot down, but it comes out like this:

```auto
Line timestamp Bolt 1 Bolt 2 Bolt 3 ..... Bolt 21
33601 2015-01-01 10:30:00 0.05
33601 2015-01-01 10:30:00 0.06 
33601 2015-01-01 10:30:00 0.05
.....
33601 2015-01-01 10:30:00 0.07
```

What I want is:

```auto
Line timestamp Bolt 1 Bolt 2 Bolt 3 ...... Bolt 21
33601 2015-01-01 10:30:00 0.05 0.06 0.05 0.07
33601 2015-01-01 10:31:23 0.04 0.06 0.04 0.05
etc.
```

Code Below… I think I am missing a Group function somewhere???

```auto
SELECT *
FROM
(
		SELECT M.Line,
		S.timestamp,
		S.SampleUUID, 
		S.AttrName, 
		CAST(S.AttrValue AS float(3)) AS fixValue, 
		CAST(substring(S.AttrName, 15, 3) AS INT) AS ATR_Index 
			FROM QUAL_SampleData S  
			INNER JOIN QUAL_Sample M 
			ON S.SampleUUID = M.SampleUUID 
			WHERE S.TimeStamp > '{Root Container.Date Range.startDate}' 
				AND S.TimeStamp < '{Root Container.Date Range.endDate}' 
				AND S.AttrName LIKE 'Thickness%' 
				AND MeasNo = '1' AND AttrValue < '1' 
) DataTable
PIVOT
(
		AVG(fixValue)
		FOR AttrName
		IN (
		[Thickness Bolt 1],[Thickness Bolt 2],[Thickness Bolt 3],[Thickness Bolt 4],[Thickness Bolt 5],[Thickness Bolt 6],
		[Thickness Bolt 7],[Thickness Bolt 8],[Thickness Bolt 9],[Thickness Bolt 10],[Thickness Bolt 11],[Thickness Bolt 12],
		[Thickness Bolt 13],[Thickness Bolt 14],[Thickness Bolt 15],[Thickness Bolt 16],[Thickness Bolt 17],[Thickness Bolt 18],
		[Thickness Bolt 19],[Thickness Bolt 20],[Thickness Bolt 21]
		)
) PivotTable

ORDER BY timestamp ASC, ATR_Index ASC
```

---

<div class="post-metadata">

**Author:** ![SeanT](https://avatars.discourse-cdn.com/v4/letter/s/df788c/32.png) [@SeanT](https://forum.inductiveautomation.com/u/SeanT)\
**Post date:** [February 9, 2015, 9:23pm UTC](https://forum.inductiveautomation.com/t/help-with-sql-and-pivot-commands/9683/2 "2015-02-09T21:23:13Z")

</div>

You can try using CTEs and joining if your database supports them. Simplified version (PostgreSQL):

[code]WITH  
b1 AS  
(SELECT time\_stamp, attrvalue as “Bolt 1”  
FROM test\_table  
WHERE attrname = ‘Bolt 1’  
AND time\_stamp BETWEEN {ts1} AND {ts2}),  
b2 AS  
(SELECT time\_stamp, attrvalue as “Bolt 2”  
FROM test\_table  
WHERE attrname = ‘Bolt 2’  
AND time\_stamp BETWEEN {ts1} AND {ts2}),  
b3 AS  
(SELECT time\_stamp, attrvalue as “Bolt 3”  
FROM test\_table  
WHERE attrname = ‘Bolt 3’  
AND time\_stamp BETWEEN {ts1} AND {ts2})

SELECT b1.time\_stamp as ts, b1.“bolt 1”, b2.“bolt 2”, b3.“bolt 3”  
FROM b1  
FULL OUTER JOIN b2 ON (b1.time\_stamp = b2.time\_stamp)  
FULL OUTER JOIN b3 ON (b2.time\_stamp = b3.time\_stamp)[/code]

Assuming for each timestamp, there is an entry for each bolt, this should work. If not, you can coalesce all the timestamp columns to make sure nothing is missing.