SQL Pivot and Unpivot help

Ignition
22

That is great when a site generates a new link like that.
I only saw that before on one other site, but it was really really helpful.
It had map icons and we could hold a button and drag an image on the map, the icon would duplicate.
Then when we had placed the changes, we could get a link to share the new look.

I had forgotten about that. I think I need to look into some of that functionality eventually.

http://sqlfiddle.com/#!18/cabb5/12
I have the singular pivot working here.

http://sqlfiddle.com/#!18/cabb5/14
I get an error using Efficiency after I added the proper parenthesis and averaging totals.
This works for me in Ignition, or maybe something slightly different.

[SQL Fiddle](https://
I got an operand error trying with OEE to determine if the pivot was the issue.

I think that Fiddle doesn't' handle this correctly, or maybe SQL in Ignition handles it when it should not.

avg(avg(D.OEE)) over(partition by Operator) as avgEffTotal,
avg(avg(D.OEE)) over() as deptAvgEff

This caused Fiddle to say directly that it had errored and not produce a link:

SELECT
Operator,

avg(avg(D.OEE)) over(partition by Operator) as avgEffTotal

FROM (
  Select Line, Operator, Efficiency, OEE 
  from OPERATOR_EFF
) as D

GROUP BY Operator

This one had an operand datatype error:
http://sqlfiddle.com/#!18/cabb5/30

I think I can't use the Over() function in Fiddle.

Might be an issue that in Fiddle, the numbers are varchar.

I found a sort of zipping method that I didn't know was possible that I want to note for later.
http://sqlfiddle.com/#!18/d8ce13/1
edit:
Since Fiddle is completely broken at this very moment as they might be doing maintenance or having bad weather?

SELECT x.a, y.b FROM (SELECT * from a) as x, (SELECT * FROM b) as y

This was the querey that zipped.
I had inserted values (1),(2), (3),(4) into table a
values (1),(2) into table B

IIRC, I got a result like:

a           b
1           1
2           1
3           1
4           1
1           2
2           2
3           2
4           2

This did not seem to work either:

SELECT
Operator,
MAX(B1) as "B1 EFF", 
MAX(B2) as "B2 EFF",  
MAX(B3) as "B3 EFF" 

FROM (
  Select Line, Operator, Efficiency, OEE 
  from OPERATOR_EFF
) as D
pivot ( max(Efficiency) for Line in (select distinct 
case when Line = 'B3' then Line else Line+','end from OPERATOR_EFF as B) 
) as pvt


GROUP BY Operator
1 Like
23

Each time I try to load Fiddle, I get a notification something went wrong and am not able to build a schema new.

On Ignition side, I got two pivots and an additional window function across all to work finally.
I used max() in the select.
I can't get Fiddle to work, and the values are varchars, so it would not work there.
However, in general, this is what worked in Ignition:

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",
  deptAvgOEE

FROM (
 Select Line, Line + '_OEE' as LineOEE, Operator, Efficiency, OEE,
 avg( avg(OEE) ) Over() as deptAvgOEE
 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
Order By Operator

I now have sums, averages, and two pivots working.
I plan to see about more, and I am pretty optimistic now that I have the group and windowed sections sorted out.

I think I have the technique down. Thanks very much for helping me.

2 Likes
24

I have the techniques.
I am struggling to have conceptual mastery.

I ordered SQL Cookbook 2nd edition to help with pivots and unpivots.

Machine |  Hot_Rejects | Cold_Rejects | Deformed_Rejects
B1      |  5           |     3        |   0
B2      |  0           |     2        |   1
B3      |  2           |     4        |   1

To look like:

Line   | Reason_Top_3            | Qty_top_3_Reasons
B1     | Hot_Rejects             |  5         
B1     | Cold_Rejects            |  3           
B1     | Deformed_Rejects        |  0    
B2     | Cold_Rejects            |  2          
B2     | Deformed_Rejects        |  1  
B2     | Hot_Rejects             |  0      
B3     | Cold_Rejects            |  4    
B3     | Hot_Rejects             |  2                
B3     | Deformed_Rejects        |  1

Used this:

Select Line, Reason, Qty from(

Select  Line
, Reason
, Qty , rank() over(partition by Line order by Qty desc)  qtyRank
from 
	(
		SELECT	machine as Line,
				Hot_Rejects,
				Cold_Rejects,
				Deformed_Rejects
		FROM yourtable
	) pivData
	UNPIVOT
	(
	Qty
	for Reason in (Hot_Rejects,Cold_Rejects,Deformed_Rejects)
	) unpiv) as S

Where qtyRank<4 and Qty>1
order by Line, Qty desc

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%

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%

Used something like:

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",
  deptAvgOEE

FROM (
 Select Line, Line + '_OEE' as LineOEE, Operator, Efficiency, OEE,
 avg( avg(OEE) ) Over() as deptAvgOEE
 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
Order By Operator

I thought with these two strategies that what I am trying to do now would be simpler.

Line   | Reason_Top_3            | Qty_top_3_Reasons
B1     | Hot_Rejects             |  5         
B1     | Cold_Rejects            |  3           
B1     | Deformed_Rejects        |  0    
B2     | Cold_Rejects            |  2          
B2     | Deformed_Rejects        |  1  
B2     | Hot_Rejects             |  0      
B3     | Cold_Rejects            |  4    
B3     | Hot_Rejects             |  2                
B3     | Deformed_Rejects        |  1

To look like:

Machine |  Hot_Rejects | Cold_Rejects | Deformed_Rejects
B1      |  5           |     3        |   0
B2      |  0           |     2        |   1
B3      |  2           |     4        |   1

I thought maybe composing this post would help me to tie it together.
Maybe it is not the same though, as I have Line, Day, and Shift involved.

25

I found a work around or at least a way to get my columns.

select Day, Shift, Line,
 sum(case code when 27 then Qty end) as 'Hot_rejects'
from(

chunk of code that got me the base information

)subq
group by Day, Shift, Line
order by Day, Shift,Line

I was using a join to get my descriptions.
I would like to make the column title dynamic from a join, but I am not certain how to do that.
In this example, 27 is the code number of my Hot_Rejects code.
I think alternatively, I could have written:
sum(case when reason in ('Hot_Rejects') then Qty end) as 'Hot_rejects'

This does get me the values I want with the right column headers though.
I just need to do this for every single one, of which I think I have thousands.

26

I am sorry for replying with a non-answer, but I also use MSSQL a whole lot and I always preferred to "pivot" using Python code than the weirdly limited Pivot statement. I'd usually use a namedQuery to get the raw data, and create a script file to call that query and pivot the data from there.

Is Pandas usable from Jython? Reshaping and pivot tables — pandas 1.5.2 documentation

Sorry again!

27

Pandas has C dependencies so not usable in Ignition.

28

Got it. I'd probably use dict then. Something like that: google app engine - Python Pivot Table without Pandas? - Stack Overflow

29

Not sure.

It is kind of interesting that section 12 of my SQL Cookbook book is on pivots and unpivots, but most the examples are using cases in the select rather than using pivot calls.

I like to get the data in SQL because it is faster.

30

@gdube
Today I learned that I also like to get the data in SQL because later someone will try to export the data to excel and if I did a script transform then the data will not look right at all in excel.

I am reworking multiple tables to utilize SQL queries and eliminate the scripts.

2 Likes