SQL Pivot and Unpivot help

Ignition
1

I have spent hours on even this one SQL pivot.
I would like to master SQL pivots or at least get much better with them.

I got one unpivot to work here a while back:

I have right now, this super fast query.
Column one shows me all the machines.
Column two shows all the operators
The next 12 columns show me all the numbers want to know.

The very fast query is like:

select Line, 
Operator,
isnull(  sum(Outfeeed*1.0)/ nullif(sum(standardVol),0)  as eff
--(11 more math selects)

where t_stamp between DATEADD(month, -6, CAST(GETDATE() AS date)) 
                  and DATEADD(minute, -1,cast(GETDATE()as datetime ))
and Operator is not null
and Operator i!=' '
group by Operator, Line

Then I spent hours trying to pivot the data.

I sort of got it working.

select Operator, Line1, Line2, LineN from(

select Line, 
Operator,
isnull(  sum(Outfeeed*1.0)/ nullif(sum(standardVol),0)  as eff


where t_stamp between DATEADD(month, -6, CAST(GETDATE() AS date)) 
                  and DATEADD(minute, -1,cast(GETDATE()as datetime ))
and Operator is not null
and Operator i!=' '
group by Operator, Line
) as src
pivot ( max(eff) for Line in (Line1, Line2, LineN) as pvt

Only gives me one of the values.
Required me to use a windowed function for the pivot.
I went with max, though I hope it is one to one as my testing appears to be.

I want to make a totals column, that I hope not to have to self join to get.
I want to get more columns too.

2

Your question seems to be SQL specific and not particularly related to Ignition. You may have more luck with a general software forum like Stack Overflow.

1 Like
3

Hard pass on that.

Here Jordan, Phil, LRose, Fragnound, and many more have been very helpful.
I like to think that sharing the tricks I learn and having the entries in the search here on this forum makes learning to implement in Ignition easier too.

I think that 90% of my issues with using Ignition are solved in SQL or Python.
As far as Ignition itself, like the core software, sometimes I need to put my query into a report or nest it in a report. Or sometimes there is a trick in Ignition that can overcome some trivial issue that daunts regular SQL like a quick binding transform too.

If my implementation were in just SQL, then I think maybe.

4

Heh. We are nicer over here. (:

However, this kind of topic really belongs in the "General Discussion" category. Consider moving it.

2 Likes
5

I plan to keep it in Ignition as I think I am doing this in Ignition on the designer specifically.
So I want to stay focused in capabilities associated with Ignition.

It starts as SQL, but it sometimes quickly diverges to something else like a script or sql and a script.

Particularly for SQL Pivots, I have been thinking there might not be a solution for fast pivots and I might have to get my regular query, then do a script to reorganize the data.

My thought is that someone crushed this issue in Ignition.

Though, I also think that if I had more access to my db, I would use Postgres among other software outside of Ignition. I would still hesitate to move to general. I think of general as more of a comradery section than a technical help section typically. Though I could be way off.

Since we got off on this topic, is there a section of the forum that is separated by a paywall?
Which sections are those?

6

I don't like the pivot operations in SQL. I much prefer the pivot operation I implemented in my view() expression function.

1 Like
7

No. But there is a section for people with Trust Level 3, which is automatic if you've been very active for a good long time.

1 Like
8

What kind of posts go in there?
Is there a great guide on connecting to SAP in there haha?

9

Chit chat and humor.

Nope.

1 Like
10

No joke. I wouldn't post on stack unless I just felt I needed a good beating.

4 Likes
11

I haven't had an issue with abuse on Stack as much as their solutions are not good.

When I did ask them about how to adjust my window of time for days when the 1st shift starts at 7am, I received terrible answers, and some were upvoted many times.

The solution that I think I got here or at lest developed mostly here on this forums to first to use format() and dateadd() on getdate(). Then later I switched to using convert() and getdate() because they are faster.

I think Phil helped me a ton with SQL once where he not only made a query with lead() and nested queries, but also had space to explain how it worked with more interaction.
Functionally, stack exchange doesn't have much for dialogue, though occasionally I see a short discussion after a post.

I have been looking on there for solutions. Until I find a way to get more row information into more columns, I will keep searching too.

I mean to say that I appreciate the help and that I think it justified to ask about SQL in Ignition when performing the SQL in Ignition because of the additional options specific to Ignition. Sorry, I get kind of wordy.

I need to check out view() again. I just loaded a backup from some other program to my local gateway though, so it will take me a bit.

I wish everyone who was really helpful on the forums had a tip feature in their profile.
I do appreciate help and it is a pretty good way to express it as well.

1 Like
12

You can try using this sample code. It's based off your first example, but it looks like you need to use UNPIVOT, not PIVOT.

CREATE TABLE #rawdata (
Machine NVARCHAR(2),
Hot_Rejects INT,
Cold_Rejects INT,
Deformed_Rejects INT
);
GO
INSERT INTO #Rawdata VALUES
('B1', 5,3,0),
('B2',0,2,1),
('B3', 2,4,1);
GO
SELECT Machine, Defect, Cnt from (
SELECT * FROM #rawdata)as r
UNPIVOT (CNT for DEFECT in (Hot_Rejects, Cold_Rejects, Deformed_Rejects) )as pvt;
GO

1 Like
13

To clarify, this time I have data like:

Line    |  Operator        | Efficiency    |   OEE
B1      |  Al              |     61%       |   62%
B2      |  Al              |     71%       |   72%
B3      |  Al              |     81%       |   82%
B1      |  Betty           |     73%       |   74%
B2      |  Betty           |     83%       |   84%
B3      |  Betty           |     93%       |   94%
B1      |  Carl            |     66%       |   67%
B2      |  Carl            |     76%       |   77%
B3      |  Carl            |     86%       |   87%

I need it to look like:

Operator    |  B1   EFF     | B1 OEE   | B2 Eff | B2 OEE   | B3 Eff | B3 OEE   | avgWtHr EFF |  avgWtHr OEE
Al          |  61%          |  62%     |   71%  | 72%      |   81%  |  82%     |   74.3%     |  75.4%  
Betty       |  73%          |  74%     |   83%  | 84%      |   93%  |  94%     |   88%       |  89%  
Carl        |  66%          |  67%     |   76%  | 77%      |  86%   |  87%     |   70%       |  72%

What I have working right now is similar to:

Operator    |  B1   EFF     | B2 Eff   | B3 Eff 
Al          |  61%          |   71%    |  81%  
Betty       |  73%          |  83%     |  93%   
Carl        |  66%          |   76%    |  86%

via:

select Operator, Line1, Line2, LineN from(

select Line, 
Operator,
isnull(  sum(Outfeeed*1.0)/ nullif(sum(standardVol),0)  as eff


where t_stamp between DATEADD(month, -6, CAST(GETDATE() AS date)) 
                  and DATEADD(minute, -1,cast(GETDATE()as datetime ))
and Operator is not null
and Operator i!=' '
group by Operator, Line
) as src
pivot ( max(eff) for Line in (Line1, Line2, LineN) as pvt

For the sake of this general code, I don't need help with the math.
I made up numbers for the example as well.
Trying to figure out how to get the rows to display correctly as the columns.

I appreciate the help @crees
Maybe you are right, and I am overthinking it.
Maye I can get the average weights by hours totaled in the primary query and sort of unpivot all data.
I have to test it next week. I thought that I had tried that and it did not work.

I will test next week and report findings back.

I think I might have to do a script binding to get the results.

14

If anybody wants to take a crack at producing the desired result, I've set up a MSSQL test database with his sample table here:

3 Likes
15

Easy, I do this all the time at work.

You just need a "dummy" column to do your second pivot off of

SELECT
  Operator,
  MAX(B1) as "B1 EFF",
  MAX(B1_OEE) as "B1 OEE",
  MAX(B2) AS "B2 EFF",
  MAX(B2_OEE) as "B2 OEE",
  MAX(B3) as "B3 EFF",
  MAX(B3_OEE) as "B3 OEE"
FROM (
  Select Line, Line + '_OEE' as LineOEE, Operator, Efficiency, OEE from OPERATOR_EFF
) D
pivot ( max(Efficiency) for Line in (B1, B2, B3) ) as pvt
pivot ( max(OEE) for LineOEE in (B1_OEE, B2_OEE, B3_OEE) ) as pvt2
GROUP BY Operator
5 Likes
16

I had no idea you could do more pivots like that!

Thank you so much, and that SQL Fiddle is amazing.

2 Likes
17

I am getting some issues trying to add some totalling columns.
I am not able to get Fiddle to show me results.

image
image1691×686 59.3 KB

In my table on Ignition, I can get the average efficiency by operator and the average overall to work with only one pivot.
image

I don't get an error with two pivots.
I get many of the columns are null, and the pivoting is wrong.

image
image1097×216 3.89 KB

18

Perhaps due to a syntax error?
image

20

I am not sure why execute doesn't work after I fixed the commas.

I get D.Efficiency multipart error or Efficiency bad column.

image
image1626×629 45 KB 

SELECT
  Operator,
  MAX(B1) as "B1 EFF", 
  MAX(B2) AS "B2 EFF",  
  MAX(B3) as "B3 EFF",
  avg(  avg(Efficiency)) over(partition by Operator) as avgEffTotal,
  avg( avg(Efficiency)) over() as deptAvgEff 
FROM (
  Select Line, Operator, Efficiency, OEE from OPERATOR_EFF
) as D
pivot ( max(Efficiency) for Line in (B1, B2, B3) ) as pvt

GROUP BY Operator

My suspicion is that Over() is not the best with Fiddle when combined with the pivots.
Though I am not certain.
Still highly likely something I did wrong.

Update Edit
Ah, I think I am maybe not aggregating all my pieces of data in the top of my query on Ignition is why maybe the double pivot isn't working haha.

I will test and get back to this tomorrow.

Thanks for helping me. I think maybe I figured it out what I did wrong at least on the Ignition side.

21

One noteworthy and great thing about Fiddle is that every time something is changed in the query, a new url is generated that is specific to that query. Therefore, it is possible to post a link directly to the query that isn't working, and if somebody is able to fix it, they can reply with a direct link to the corrected query.

Example:
• Here is the link to the Incorrect syntax near ',' error: http://sqlfiddle.com/#!18/b389c8/72
• Here is the link to the D.Efficiency multipart error: http://sqlfiddle.com/#!18/b389c8/77

Notice the [...]/72 and the [...]/77

To reset the number to [...]/1, so that parallel solving attempts can be monitored (or to hide how many attempts have been made so far), simply make a slight change anywhere to the Create Table query and rebuild the schema.

Example:
• Here is the link to the Incorrect syntax near ',' error after adding a single space and rebuilding the schema:
http://sqlfiddle.com/#!18/cabb5/1

I know this doesn't at all address your original question, but perhaps it will make this problem and future problems more accessible for those who are willing to help.

2 Likes