# Data Sorting in Ignition Report Table

**URL:** <https://forum.inductiveautomation.com/t/data-sorting-in-ignition-report-table/50702>\
**Category:** Ignition\
**Tags:** database, perspective, ignition81\
**Created:** [September 8, 2021, 9:24am UTC](https://forum.inductiveautomation.com/t/data-sorting-in-ignition-report-table/50702 "2021-09-08T09:24:12Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![shabir.ahmed](https://avatars.discourse-cdn.com/v4/letter/s/a88e4f/32.png) [@shabir.ahmed](https://forum.inductiveautomation.com/u/shabir.ahmed)\
**Post date:** [September 8, 2021, 9:24am UTC](https://forum.inductiveautomation.com/t/data-sorting-in-ignition-report-table/50702/1 "2021-09-08T09:24:12Z")

</div>

Dear Ignition family,

I am making one report to show consumption of power each week, I need to show week number as column and equipment as rows(more than 50 equipment). My Query returns data in following view  
Query:  
SELECT DATEPART(WEEK, t\_stamp) as wk, max(Rinser\_Recovery) as Rinser, max(CUB\_Tank) as CUB, max(UGT) as UGT, max(CSD) as CSD, max(AQF) as AQF, max(wasa\_ro) as WASA\_RO, max(WWTP) as WWTP  
from ICE\_PAC group by DATEPART(WEEK, t\_stamp) order by DATEPART(WEEK, t\_stamp)

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/2/f2a4242aa07f3dbc31ccb2317402f9cc1a10b715.png)

I need to show data in following view in Ignition Report(WK number in Column and equipment in rows):

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/3/d35fae706d30f7c6bcf5c8c5c1b4991058aa81ec.png)

Thanks!

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [September 8, 2021, 10:48am UTC](https://forum.inductiveautomation.com/t/data-sorting-in-ignition-report-table/50702/2 "2021-09-08T10:48:33Z")

</div>

You should be able to use a pivot in your query.

> **[SQL Server PIVOT Operator Explained Clearly By Practical Examples](https://www.sqlservertutorial.net/sql-server-basics/sql-server-pivot/)**
>
> This tutorial shows you step by step how to use the SQL Server PIVOT operator to convert rows to columns.

---

<div class="post-metadata">

**Author:** ![shabir.ahmed](https://avatars.discourse-cdn.com/v4/letter/s/a88e4f/32.png) [@shabir.ahmed](https://forum.inductiveautomation.com/u/shabir.ahmed)\
**Post date:** [September 9, 2021, 9:45am UTC](https://forum.inductiveautomation.com/t/data-sorting-in-ignition-report-table/50702/3 "2021-09-09T09:45:54Z")

</div>

@JordanCClark thanks for reply, I have seen the example, its for two tables and sorry i missed to add some more information. Let me clarify more, in my table I need column name as equipment and max values to be show against equipment. where column name need to be week number converted from timestamp. I am able to sort my data wrt to week against each equipment in column like below pic  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/2/720a7b5a9388973b50620e4821c59aa4ac0be22d.png)  
but i need data to be showed like below:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/7/b75b7cff58adb6fcc780b0af8480d95d97c7b136.png)

---

<div class="post-metadata">

**Author:** ![shabir.ahmed](https://avatars.discourse-cdn.com/v4/letter/s/a88e4f/32.png) [@shabir.ahmed](https://forum.inductiveautomation.com/u/shabir.ahmed)\
**Post date:** [September 9, 2021, 9:53am UTC](https://forum.inductiveautomation.com/t/data-sorting-in-ignition-report-table/50702/4 "2021-09-09T09:53:36Z")

</div>

I should like to add one more thing with help of pivot, I was able to sort one column as row but in actual scenario I have more than 50, here was my query  
–Pivot by WK number for one machine

SELECT ‘WASA\_RO’ AS machine,  
[37] as WK37, [36] as WK36  
FROM  
(  
SELECT DATEPART(WEEK, t\_stamp) as wk, max(WASA\_RO) as mac from ICE\_PAC group by DATEPART(WEEK, t\_stamp)  
) AS SourceTable  
PIVOT  
(  
max(mac) for  
wk IN ([37], [36])  
) AS PivotTable;

and the answer

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/0/700833f47a475bbd7fe16a3c9e7e436057e12100.png)

but i am looking to all add my columns except t\_stamp under machines (as per snap above)

---

<div class="post-metadata">

**Author:** ![mcgheeiv](https://avatars.discourse-cdn.com/v4/letter/m/71c47a/32.png) [@mcgheeiv](https://forum.inductiveautomation.com/u/mcgheeiv)\
**Post date:** [September 9, 2021, 9:53am UTC](https://forum.inductiveautomation.com/t/data-sorting-in-ignition-report-table/50702/5 "2021-09-09T09:53:48Z")

</div>

Try grouping and ordering by year as well as week.

---

<div class="post-metadata">

**Author:** ![shabir.ahmed](https://avatars.discourse-cdn.com/v4/letter/s/a88e4f/32.png) [@shabir.ahmed](https://forum.inductiveautomation.com/u/shabir.ahmed)\
**Post date:** [September 9, 2021, 9:59am UTC](https://forum.inductiveautomation.com/t/data-sorting-in-ignition-report-table/50702/6 "2021-09-09T09:59:38Z")

</div>

@mcgheeiv thanks for your input can you please review my query above and suggest specifically what i should need to do to cover all my columns under machines column.

---

<div class="post-metadata">

**Author:** ![mcgheeiv](https://avatars.discourse-cdn.com/v4/letter/m/71c47a/32.png) [@mcgheeiv](https://forum.inductiveautomation.com/u/mcgheeiv)\
**Post date:** [September 9, 2021, 10:21am UTC](https://forum.inductiveautomation.com/t/data-sorting-in-ignition-report-table/50702/7 "2021-09-09T10:21:33Z")

</div>

It might be easier to pull the data in with a simpler query

```auto
SELECT DATEPART(YEAR), DATEPART(WEEK), MAX(col_1), MAX(col_2), etc
FROM ICE_PAC
WHERE t_stamp >= date_beg and t_stamp <= date_end
GROUP BY YEAR, WEEK
ORDER BY YEAR, WEEK

```

Then use a python script to perform the pivot.

Pivots take 3 columns of data and turn them into a matrix with one column forming the matrix column headings (week), another being the row labels (machine), and another filling the cells according to the aggregation. Your columns should be YYYY-WW, machine name, value. Since your data is in 50 columns (WASA, CUB, AQF, etc.) a UNION could be used to create the SourceTable (syntax is not perfect):

```auto
SELECT 'WASA' as machine, CAST(DATEPART(YEAR, t_stamp) AS VARCHAR) + '-' + CAST(DATEPART(WEEK, t_stamp) AS VARCHAR)
    , MAX(WASA) AS v_float
UNION
SELECT 'CUB' as machine, CAST(DATEPART(YEAR, t_stamp) AS VARCHAR) + '-' + CAST(DATEPART(WEEK, t_stamp) AS VARCHAR)
    , MAX(CUB) AS v_float
UNION
SELECT 'AQF' as machine, CAST(DATEPART(YEAR, t_stamp) AS VARCHAR) + '-' + CAST(DATEPART(WEEK, t_stamp) AS VARCHAR)
    , MAX(AQF) AS v_float
WHERE t_stamp >= @date_beg and t_stamp <= @date_end
GROUP BY DATEPART(YEAR, t_stamp), DATEPART(WEEK, t_stamp)
ORDER BY DATEPART(YEAR, t_stamp), DATEPART(WEEK, t_stamp)
ETC, ETC, ETC (for all 50 machines)

```

Then all you need is to create the YEAR and WEEK range for use in the outer SELECT and the IN clause.

---

<div class="post-metadata">

**Author:** ![mcgheeiv](https://avatars.discourse-cdn.com/v4/letter/m/71c47a/32.png) [@mcgheeiv](https://forum.inductiveautomation.com/u/mcgheeiv)\
**Post date:** [September 9, 2021, 10:26am UTC](https://forum.inductiveautomation.com/t/data-sorting-in-ignition-report-table/50702/8 "2021-09-09T10:26:07Z")

</div>

If the database table can be changed, a table with the following 3 columns would make the pivot solution easier:

- machine\_name VARCHAR(50)
- t\_stamp DATETIME
- machine\_value FLOAT

```auto
SELECT * FROM
(
	SELECT machine_name, function_of_year_week(t_stamp) AS year_week, MAX(machine_value) AS machine_value 
	FROM ICE_PAC
	WHERE t_stamp >= '{Root Container.date_beg}' AND t_stamp <= '{Root Container.date_end}'
	GROUP BY xxx
	ORDER BY xxx
) t
PIVOT(
	MAX(machine_value)
	FOR year_week IN ({Root Container.dates_pivot})
) as P

```

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [September 9, 2021, 11:36am UTC](https://forum.inductiveautomation.com/t/data-sorting-in-ignition-report-table/50702/9 "2021-09-09T11:36:51Z")

</div>

```python
sampleHeaders = ['wk', 'WASA', 'CUB', 'AQF']
sampleData = [
              [36, 5.168243E7, 4.598042E07, 8.83453E7],
              [37, 44544, 47360, 82432]
             ]
             
dataIn = system.dataset.toDataSet(sampleHeaders, sampleData)

colNamesIn = list(dataIn.getColumnNames())

# Initialize new headers and data
headers = ['Equipment'] + dataIn.getColumnAsList(colNamesIn.index('wk'))
data = []
# Remove the pivot column name for easier iteration of the dataset.
colNamesIn.remove('wk')

# Iterate through the columns
for col in colNamesIn:
	newRow = [col]
	# Iterate throu the rows
	for row in xrange(dataIn.rowCount):
		newRow.append(dataIn.getValueAt(row, col))
	data.append(newRow)

dataOut = system.dataset.toDataSet(headers, data)

```

```nohighlight
         Sample Dataset

row | wk | WASA | CUB | AQF       
-----------------------------------------------
0 | 36 | 51682430.0 | 45980420.0 | 88345300.0
1 | 37 | 44544.0 | 47360.0 | 82432.0   

          Resultant Dataset

row | Equipment | 36 | 37     
--------------------------------------
0 | WASA | 51682430.0 | 44544.0
1 | CUB | 45980420.0 | 47360.0
2 | AQF | 88345300.0 | 82432.0
```

---

<div class="post-metadata">

**Author:** ![shabir.ahmed](https://avatars.discourse-cdn.com/v4/letter/s/a88e4f/32.png) [@shabir.ahmed](https://forum.inductiveautomation.com/u/shabir.ahmed)\
**Post date:** [September 12, 2021, 10:42am UTC](https://forum.inductiveautomation.com/t/data-sorting-in-ignition-report-table/50702/10 "2021-09-12T10:42:54Z")

</div>

Thanks @JordanCClark@mcgheeiv for helping me alot. I am pretty new with scripting but for sure will read and try script made by @JordanCClark. I have tried query method with below queries and found results.  
SELECT ‘CAN1’ as machine, DATEPART(WEEK, t\_stamp) as wk, DATEPART(YEAR, t\_stamp) as yr  
, MAX(CAN1\_INC) AS Reading, (select max(CAN1\_INC)-min(CAN1\_INC) from CANGroup where DATEPART(WEEK, t\_stamp)=37) as Cons from CANGroup  
where DATEPART(WEEK, t\_stamp)=37  
GROUP BY DATEPART(YEAR, t\_stamp), DATEPART(WEEK, t\_stamp)

UNION  
SELECT ‘CAN2’ as machine, DATEPART(WEEK, t\_stamp) as wk, DATEPART(YEAR, t\_stamp) as yr  
, MAX(CAN2\_INC) AS Reading, (select max(CAN2\_INC)-min(CAN2\_INC) from CANGroup where DATEPART(WEEK, t\_stamp)=37) as Cons from CANGroup  
where DATEPART(WEEK, t\_stamp)=37  
GROUP BY DATEPART(YEAR, t\_stamp), DATEPART(WEEK, t\_stamp)  
UNION  
SELECT ‘CAN3’ as machine, DATEPART(WEEK, t\_stamp) as wk, DATEPART(YEAR, t\_stamp) as yr  
, MAX(CAN3\_INC) AS Reading, (select max(CAN3\_INC)-min(CAN3\_INC) from CANGroup where DATEPART(WEEK, t\_stamp)=37) as Cons from CANGroup  
where DATEPART(WEEK, t\_stamp)=37  
GROUP BY DATEPART(YEAR, t\_stamp), DATEPART(WEEK, t\_stamp)  
UNION  
SELECT ‘Glass’ as machine, DATEPART(WEEK, t\_stamp) as wk, DATEPART(YEAR, t\_stamp) as yr  
, MAX(Glass\_INC) AS Reading, (select max(Glass\_INC)-min(Glass\_INC) from CANGroup where DATEPART(WEEK, t\_stamp)=37) as Cons from CANGroup  
where DATEPART(WEEK, t\_stamp)=37  
GROUP BY DATEPART(YEAR, t\_stamp), DATEPART(WEEK, t\_stamp)  
Below was the result, I have passed week number and year parameters by Simple query and can see my table in Report. thanks again @JordanCClark and @mcgheeiv

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/e/8ed5e30a9f8ddd5661e56c3464d488ed57e98042.png)