# Adding Pivoted Data to a Report

**URL:** <https://forum.inductiveautomation.com/t/adding-pivoted-data-to-a-report/81252>\
**Category:** Ignition\
**Created:** [October 31, 2023, 5:43pm UTC](https://forum.inductiveautomation.com/t/adding-pivoted-data-to-a-report/81252 "2023-10-31T17:43:00Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Andrew\_Benzing](https://avatars.discourse-cdn.com/v4/letter/a/34f0e0/32.png) [@Andrew\_Benzing](https://forum.inductiveautomation.com/u/Andrew_Benzing)\
**Post date:** [October 31, 2023, 5:43pm UTC](https://forum.inductiveautomation.com/t/adding-pivoted-data-to-a-report/81252/1 "2023-10-31T17:43:00Z")

</div>

I have this Microsoft SQL Server query that generates a pivot table.  
"SELECT Program,  
ISNULL([1], 0) AS [1], ISNULL([2], 0) AS [2], ISNULL([3], 0) AS [3], ISNULL([4], 0) AS [4], ISNULL([5], 0) AS [5],  
ISNULL([6], 0) AS [6], ISNULL([7], 0) AS [7], ISNULL([8], 0) AS [8], ISNULL([9], 0) AS [9], ISNULL([10], 0) AS [10],  
ISNULL([11], 0) AS [11], ISNULL([12], 0) AS [12], ISNULL([13], 0) AS [13], ISNULL([14], 0) AS [14], ISNULL([15], 0) AS [15],  
ISNULL([16], 0) AS [16], ISNULL([17], 0) AS [17], ISNULL([18], 0) AS [18], ISNULL([19], 0) AS [19], ISNULL([20], 0) AS [20],  
ISNULL([21], 0) AS [21], ISNULL([22], 0) AS [22], ISNULL([23], 0) AS [23], ISNULL([24], 0) AS [24],  
ISNULL([1], 0) + ISNULL([2], 0) + ISNULL([3], 0) + ISNULL([4], 0) + ISNULL([5], 0) + ISNULL([6], 0) +  
ISNULL([7], 0) + ISNULL([8], 0) + ISNULL([9], 0) + ISNULL([10], 0) + ISNULL([11], 0) + ISNULL([12], 0) +  
ISNULL([13], 0) + ISNULL([14], 0) + ISNULL([15], 0) + ISNULL([16], 0) + ISNULL([17], 0) + ISNULL([18], 0) +  
ISNULL([19], 0) + ISNULL([20], 0) + ISNULL([21], 0) + ISNULL([22], 0) + ISNULL([23], 0) + ISNULL([24], 0) AS Total  
FROM (  
SELECT  
ISNULL(Count, 0) AS Count,  
Program,  
DATEPART(HOUR, Time) AS Hour  
FROM  
my\_table  
WHERE  
Time \>= DATEADD(HOUR, -24, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))  
AND Time \< DATEADD(HOUR, 7, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))  
) r  
PIVOT(  
SUM(Count)  
FOR Hour IN (  
[1], [2], [3], [4], [5], [6], [7], [8], [9], [10],  
[11], [12], [13], [14], [15], [16], [17], [18], [19], [20],  
[21], [22], [23], [24]  
)  
) AS pivot\_table;"  
This generates the pivoted data below.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/3/93f122a770c22facc2ae1480330827724d3e2da7.png)  
I would like to add this pivot table to an Ignition Report. I am having trouble accomplishing this. Would it be simpler to just query the data (without pivoting it) into a CrossTab and then pivot in the CrossTab?  
New to reports - any advice helps. Thanks!

---

<div class="post-metadata">

**Author:** ![Tim\_Carlton](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tim_carlton/32/52835_2.png) [@Tim\_Carlton](https://forum.inductiveautomation.com/u/Tim_Carlton)\
**Post date:** [October 31, 2023, 5:58pm UTC](https://forum.inductiveautomation.com/t/adding-pivoted-data-to-a-report/81252/2 "2023-10-31T17:58:22Z")

</div>

What have you tried?  
What results did you get?

---

<div class="post-metadata">

**Author:** ![drojas](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/drojas/32/3403_2.png) [@drojas](https://forum.inductiveautomation.com/u/drojas)\
**Post date:** [October 31, 2023, 7:26pm UTC](https://forum.inductiveautomation.com/t/adding-pivoted-data-to-a-report/81252/3 "2023-10-31T19:26:21Z")

</div>

Yes, the reporting capabilities are pretty good to summarize/totalize values on a table for you, just return the raw data and let it do the pivot and totals. Not sure what flavor of SQL you use, but look into generate\_series function to clean up that query.