# Sum Columns and Convert to Rows in MSSQL 2005

**URL:** <https://forum.inductiveautomation.com/t/sum-columns-and-convert-to-rows-in-mssql-2005/3233>\
**Category:** Archived Topics\
**Created:** [August 30, 2011, 5:59pm UTC](https://forum.inductiveautomation.com/t/sum-columns-and-convert-to-rows-in-mssql-2005/3233 "2011-08-30T17:59:38Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Duffanator](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/duffanator/32/3560_2.png) [@Duffanator](https://forum.inductiveautomation.com/u/Duffanator)\
**Post date:** [August 30, 2011, 5:59pm UTC](https://forum.inductiveautomation.com/t/sum-columns-and-convert-to-rows-in-mssql-2005/3233/1 "2011-08-30T17:59:38Z")

</div>

Hey all,

I have set up a transaction that runs once per day and collects downtimes and places each downtime into a Column. Every row in the table is a days worth of times, which is what I wanted to do. Here is what the table looks like:

 ![](https://us1.discourse-cdn.com/inductiveautomation/original/1X/e4260c93cd7528a0ac6ab98d0b82db79d53ce67a.PNG)  

Now I want to make a downtime report but the charts on the report don’t seem to like this table format, I can sum all of the columns but I’m having a hard time getting it to display correctly in the report. How do I sum the columns and then turn them into rows? so instead of:

E-stop 1 E-stop 2 E-stop 3 E-stop 4 etc…  
1 2 1 4  
2 3 5 2

I would like:

E-stop 1 3  
E-stop 2 5  
E-stop 3 6  
E-stop 4 6

I’m no SQL guru and I can’t figure out how to accomplish this task. Any help or tips would be appreciated, thanks!

---

<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:** [August 30, 2011, 6:37pm UTC](https://forum.inductiveautomation.com/t/sum-columns-and-convert-to-rows-in-mssql-2005/3233/2 "2011-08-30T18:37:11Z")

</div>

This isn’t well supported in SQL, but you can write a simple script to do this for you. Something like:

`rawData = system.db.runQuery("SELECT sum(Minutes_Estop__1_Active), sum(Minutes_Estop__2_Active), sum(Minutes_Estop__3_Active), ...")
header = ["E-stop", "sum"]
newData = []
newData.append(["E-stop 1", rawData[0][0] ])
newData.append(["E-stop 2", rawData[0][1] ])
newData.append(["E-stop 3", rawData[0][2] ])
finishedData = system.dataset.toPyDataSet(header, newData)`

---

<div class="post-metadata">

**Author:** ![Duffanator](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/duffanator/32/3560_2.png) [@Duffanator](https://forum.inductiveautomation.com/u/Duffanator)\
**Post date:** [August 30, 2011, 8:07pm UTC](https://forum.inductiveautomation.com/t/sum-columns-and-convert-to-rows-in-mssql-2005/3233/3 "2011-08-30T20:07:23Z")

</div>

Cool, thank you!

I put that code into the property change event handler on a date selector but now I’m getting some kind of date format error.

With this code:

`start = event.source.date
stop = event.source.parent.getComponent('FlowStop').date
rawData = system.db.runQuery("SELECT SUM(Minutes_Estop__10_Active), SUM(Minutes_Estop__11_Active),SUM(Minutes_Estop__1_Active), SUM(Minutes_Estop__2_Active),SUM(Minutes_Estop__3_Active), SUM(Minutes_Estop__4_Active),SUM(Minutes_Estop__5_Active), SUM(Minutes_Estop__6_Active),SUM(Minutes_Estop__7_Active), SUM(Minutes_Estop__8_Active),SUM(Minutes_Estop__9_Active), SUM(Minutes_Hydraulic_Timeout_Active),SUM(Minutes_Landing_Conveyor_Down), SUM(Minutes_Left_Loin_Puller_Down),SUM(Minutes_Right_Loin_Puller_Down) FROM Cutfloor_EandD WHERE t_stamp >= '" + start + "' and t_stamp <= '" + stop + "'")`

I get this error:

Traceback (innermost last):

File “event:propertyChange”, line 3, in ?

TypeError: **add** nor **radd** defined for these operands

Ignition v7.2.8 (b178)  
Java: Sun Microsystems Inc. 1.6.0\_24

I know this is somekind of typecase error, so I tried making the start date a string by doing str(start), but then I get a different error:

Caused by: com.inductiveautomation.ignition.client.gateway\_interface.GatewayException: Gateway Error 500: Conversion failed when converting datetime from character string.

So now I’m stuck again… I can’t figure out how to pass the date from a date selection into the SQL query. ☹

---

<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:** [August 30, 2011, 8:25pm UTC](https://forum.inductiveautomation.com/t/sum-columns-and-convert-to-rows-in-mssql-2005/3233/4 "2011-08-30T20:25:44Z")

</div>

It’s saying that you cannot add a string to a date. Try using python to stuff the date into the string like this:

`start = event.source.date
stop = event.source.parent.getComponent('FlowStop').date
rawData = system.db.runQuery("SELECT SUM(Minutes_Estop__10_Active), SUM(Minutes_Estop__11_Active),SUM(Minutes_Estop__1_Active), SUM(Minutes_Estop__2_Active),SUM(Minutes_Estop__3_Active), SUM(Minutes_Estop__4_Active),SUM(Minutes_Estop__5_Active), SUM(Minutes_Estop__6_Active),SUM(Minutes_Estop__7_Active), SUM(Minutes_Estop__8_Active),SUM(Minutes_Estop__9_Active), SUM(Minutes_Hydraulic_Timeout_Active),SUM(Minutes_Landing_Conveyor_Down), SUM(Minutes_Left_Loin_Puller_Down),SUM(Minutes_Right_Loin_Puller_Down) 
FROM Cutfloor_EandD WHERE t_stamp >= '%s' and t_stamp <= '%s'" %(start, stop))`

---

<div class="post-metadata">

**Author:** ![Duffanator](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/duffanator/32/3560_2.png) [@Duffanator](https://forum.inductiveautomation.com/u/Duffanator)\
**Post date:** [August 30, 2011, 9:34pm UTC](https://forum.inductiveautomation.com/t/sum-columns-and-convert-to-rows-in-mssql-2005/3233/5 "2011-08-30T21:34:43Z")

</div>

Tried that, same thing. It looks like it’s actually a MSSQL problem. For some reason it doesn’t like the datetime coming from the date property:

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting datetime from character string.

This is what comes up in the error dialog box for the where clause:

WHERE t\_stamp \>= ‘Sun Aug 28 08:49:38 EDT 2011’ and t\_stamp \<= ‘Wed Aug 31 08:49:38 EDT 2011’, IgnitionDB, )

Doesn’t look any different to me than using that propery binding in a SQL query…

---

<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:** [August 30, 2011, 9:48pm UTC](https://forum.inductiveautomation.com/t/sum-columns-and-convert-to-rows-in-mssql-2005/3233/6 "2011-08-30T21:48:00Z")

</div>

Yeah, the problem is that you get a format like ‘Sun Aug 28 08:49:38 EDT 2011’ out of your start and end date components. You can convert it to this format before putting it into the SQL query: ‘2011-08-28 08:49:38’. The easiest way to do this is to create a dynamic property (string) and use the dateFormat() expression function.

---

<div class="post-metadata">

**Author:** ![Duffanator](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/duffanator/32/3560_2.png) [@Duffanator](https://forum.inductiveautomation.com/u/Duffanator)\
**Post date:** [August 31, 2011, 1:41am UTC](https://forum.inductiveautomation.com/t/sum-columns-and-convert-to-rows-in-mssql-2005/3233/7 "2011-08-31T01:41:41Z")

</div>

That worked, thanks!