title: PIVOT returns NULL When Converting Rows to Columns
body: <p>Using the following example data:</p>
  <pre><code>INVNUM  ORDNUM  SHIPNUM INVLINE CHGCODE TAXBODY TAXRATE
  I1      O1      0       1       0       36      4.00
  I1      O1      0       1       0       51000   4.50
  I1      O1      0       1       0       359071  0.37
  I2      O2      0       1       0       13      4.00
  I2      O2      0       1       0       211      .25
  I3      O1      1       1       0       36      4.00
  I3      O1      1       1       0       51000   4.50
  I3      O1      1       2       A       36      4.00
  I3      O1      1       2       A       51000   4.50
  I4      O1      0       1       0       359071  6.35
  I5      O4      0       1       0       6       6.00
  I5      O4      0       1       0       65      0.25
  I5      O4      0       1       0       AIHK0   1.00
  I5      O4      0       1       0       EMBA0   0.50
  I5      O4      0       1       0       EMTQ0   1.00
  </code></pre>
  <p>There can be up to 10 TAXBODY rows for each INVNUM, ORDNUM, SHIPNUM, INVLINE, CHGCODE combination.  In other words, there can be up to 10 but there may be less.</p>
  <p>Using I2 as an example, I would like the result to be:</p>
  <pre><code>INVNUM  ORDNUM  SHIPNUM INVLINE CHGCODE TAXBODY0 TAXBODY1...TAXBODY9 TAXRATE0 TAXRATE1...TAXRATE9
  I2      O2      0       1       0       13       211        NULL     4.00     .25        NULL
  </code></pre>
  <p>I attempted to start small so I could test using PIVOT to get me just the tax rate and I'm getting back NULL in all the TAXRATEx columns.  Here is my SQL statement I used:</p>
  <pre><code>SELECT * FROM (
    SELECT
      [invnum],
      [ordnum],
      [shipnum],
      [taxbody],
      [taxrate]
    FROM #mytable
  ) TaxDetails
  PIVOT (
    AVG([taxrate])
    FOR [taxbody]
    IN (
      [taxrate0],
      [taxrate1],
      [taxrate2],
      [taxrate3],
      [taxrate4],
      [taxrate5],
      [taxrate6],
      [taxrate7],
      [taxrate8],
      [taxrate9]
    )) AS PivotTable
  </code></pre>
  <p>I realize it doesn't contain everything I need for my unique combination but again, I was trying to start out small for testing purposes.  When I run the query, I end up with NULL in TAXRATE0 through TAXRATE9.  It also doesn't seem to matter what aggregate function I use, e.g. SUM or MAX.  I've not used PIVOT before so I'm sure I'm doing something wrong.</p>
  <p>I am requesting help figuring out why my SQL statement doesn't work and ultimately would like help, e.g. links or examples, with the following end result:</p>
  <ol>
  <li>One row with 10 TAXBODY and TAXRATE columns for each INVNUM, ORDNUM, SHIPNUM, INVLINE, CHGCODE combination as mentioned above in the example.</li>
  <li>The table should allow NULL in the TAXBODYx and TAXRATEx columns where I don't have a value, i.e. I only have 2 tax body rows out of a possible 10.</li>
  <li>To be able to use the query or a stored procedure to update the appropriate TAXBODY and TAXRATE column(s) in a table that already exists.  The existing table already supports the ability to hold values from multiple TAXBODY and TAXRATE columns.</li>
  </ol>
  <p>I've found a couple examples of using PIVOT but they don't seem to work for me.  I'm guessing it's because I don't understand how PIVOT works.  Resources, e.g. links, would be appreciated as well as an explanation if you provide examples to help get me on the right path.</p>
  <p><a href="https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/" rel="nofollow noreferrer">Basic Example</a></p>
  <p><a href="https://docs.microsoft.com/en-us/archive/blogs/kenobonn/pivot-on-two-or-more-fields-in-sql-server" rel="nofollow noreferrer">An example that pivots multiple columns</a></p>