title: SQL Server 2000: Pivot with conditions
body: <p>I searched around but couldn't find a similar answer.</p>
  
  <p>What's the recommended optimal approach to pivot in SQL Server 2000 with conditions to exclude NULL and 'NA' string?</p>
  
  <p>I'd like to go from this (select only order_id = 100 and line_no = 1):</p>
  
  <pre><code>+----------+---------+-----+------+-----+------+----+
  | order_id | line_no |  a  |  b   |  c  |  d   | e  |
  +----------+---------+-----+------+-----+------+----+
  |      100 |       1 | 123 | NULL | NA  | 456  | X1 |
  |      101 |       1 | 789 | NA   | 123 | NULL | BB |
  +----------+---------+-----+------+-----+------+----+
  </code></pre>
  
  <p>To this:</p>
  
  <pre><code>+----------+---------+------+--------+
  | order_id | line_no | type | config |
  +----------+---------+------+--------+
  |      100 |       1 | a    | 123    |
  |      100 |       1 | d    | 456    |
  |      100 |       1 | e    | X1     |
  +----------+---------+------+--------+
  </code></pre>