title: Spark sql pivot
body: <p>I have this <a href="https://drive.google.com/file/d/1d4bycNUH-DYQNbwFJdKEz4cG6ATanpyI/view?usp=sharing" rel="nofollow noreferrer">data</a> where I am trying to do a pivot using spark sql. The end result needs to be this way .</p>
  
  <pre><code>+------------------+------------------+------------------+------------------+------------------+
  |Product           |AVG_CENTRAL       |AVG_EAST          |AVG_WEST          |avg(Autonumber)   |
  +------------------+------------------+------------------+------------------+------------------+
  |Air Purifiers     |300.3333333333333 |449.875           |348.5             |399.7692307692308 |
  |Art Supplies      |352.42857142857144|349.48837209302326|324.94444444444446|344.14666666666665|
  |Baby Food         |372.0             |null              |null              |372.0             |
  |Beverages         |320.0             |409.0             |null              |379.3333333333333 |
  |Binder Clips      |70.5              |551.8571428571429 |494.0             |453.8181818181818 |
  |Binding Machines  |null              |null              |361.0             |361.0             |
  |Binding Supplies  |610.0             |462.2             |616.0             |529.8888888888889 |
  |Biscuits          |505.0             |469.5             |null              |481.3333333333333 |
  |Bread             |null              |266.0             |328.0             |297.0             |
  |Business Envelopes|421.0             |356.25            |null              |369.2             |
  |Cakes             |null              |314.0             |null              |314.0             |
  |Cardboard Storage |409.0             |null              |null              |409.0             |
  |Cereals           |583.0             |356.38461538461536|278.6666666666667 |356.0             |
  |Clocks            |null              |220.2             |445.5             |284.57142857142856|
  |Colored Envelopes |615.0             |524.0             |222.0             |471.25            |
  |Computer Paper    |358.84615384615387|370.02564102564105|377.6666666666667 |369.9142857142857 |
  |Copy Paper        |300.2857142857143 |454.8888888888889 |223.55555555555554|361.8235294117647 |
  |End Tables        |null              |36.0              |234.0             |135.0             |
  |File Labels       |351.8333333333333 |325.52941176470586|379.0             |344.4193548387097 |
  |Frames            |471.0             |258.5             |709.0             |325.84615384615387|
  +------------------+------------------+------------------+------------------+------------------+
  </code></pre>
  
  <p>I tried the following query for pivot.</p>
  
  <pre><code>spark.sql("""SELECT *  FROM (SELECT Product,Region,Autonumber FROM Test) TEST PIVOT (avg(Autonumber) FOR Region in ('Central' AVG_CENTRAL, 'East' AVG_EAST, 'West' AVG_WEST  )) ORDER BY PRODUCT""")
  </code></pre>
  
  <p>The resulting dataframe was this -></p>
  
  <pre><code>+------------------+------------------+------------------+------------------+
  |           Product|       AVG_CENTRAL|          AVG_EAST|          AVG_WEST|
  +------------------+------------------+------------------+------------------+
  |     Air Purifiers| 300.3333333333333|           449.875|             348.5|
  |      Art Supplies|352.42857142857144|349.48837209302326|324.94444444444446|
  |         Baby Food|             372.0|              null|              null|
  |         Beverages|             320.0|             409.0|              null|
  |      Binder Clips|              70.5| 551.8571428571429|             494.0|
  |  Binding Machines|              null|              null|             361.0|
  |  Binding Supplies|             610.0|             462.2|             616.0|
  |          Biscuits|             505.0|             469.5|              null|
  |             Bread|              null|             266.0|             328.0|
  |Business Envelopes|             421.0|            356.25|              null|
  |             Cakes|              null|             314.0|              null|
  | Cardboard Storage|             409.0|              null|              null|
  |           Cereals|             583.0|356.38461538461536| 278.6666666666667|
  |            Clocks|              null|             220.2|             445.5|
  | Colored Envelopes|             615.0|             524.0|             222.0|
  |    Computer Paper|358.84615384615387|370.02564102564105| 377.6666666666667|
  |        Copy Paper| 300.2857142857143| 454.8888888888889|223.55555555555554|
  |        End Tables|              null|              36.0|             234.0|
  |       File Labels| 351.8333333333333|325.52941176470586|             379.0|
  |            Frames|             471.0|             258.5|             709.0|
  +------------------+------------------+------------------+------------------+
  </code></pre>
  
  <p>I wanted to get the avg of the Autonumber column for each product which I did after these steps.</p>
  
  <pre><code>spark.sql("""SELECT PRODUCT , AVG(Autonumber) from test group by product""").show
  
  +--------------------+------------------+
  |             PRODUCT|   avg(Autonumber)|
  +--------------------+------------------+
  |Fruits and Vegeta...|401.09541984732823|
  |              Clocks|284.57142857142856|
  |         File Labels| 344.4193548387097|
  |        Art Supplies|344.14666666666665|
  | Specialty Envelopes|             247.0|
  |          Copy Paper| 361.8235294117647|
  |      Computer Paper| 369.9142857142857|
  |        Highlighters|              17.5|
  |     Standard Labels|400.42857142857144|
  |    Portable Storage|             295.4|
  |             Pencils|419.14285714285717|
  |  Round Ring Binders|292.93333333333334|
  |                Meat| 365.1111111111111|
  |       Writings Pads| 336.5833333333333|
  |             Cereals|             356.0|
  |          End Tables|             135.0|
  |    Storage Cabinets|             267.5|
  |              Frames|325.84615384615387|
  |       Office Chairs|             376.0|
  |       Air Purifiers| 399.7692307692308|
  +--------------------+------------------+
  
  scala&gt; df1.join(df2 , "product").show(false)
  </code></pre>
  
  <p>Only then I could acheive my final result . 
  Is there any way I can acheive this in the pivot query itself ?
  Any help would be great.
  Also its really difficult to find blog posts on the sql syntax of pivot in spark. </p>