SQL Server 2000: Pivot with conditions

Question

I searched around but couldn't find a similar answer.

What's the recommended optimal approach to pivot in SQL Server 2000 with conditions to exclude NULL and 'NA' string?

I'd like to go from this (select only order_id = 100 and line_no = 1):

+----------+---------+-----+------+-----+------+----+
| order_id | line_no |  a  |  b   |  c  |  d   | e  |
+----------+---------+-----+------+-----+------+----+
|      100 |       1 | 123 | NULL | NA  | 456  | X1 |
|      101 |       1 | 789 | NA   | 123 | NULL | BB |
+----------+---------+-----+------+-----+------+----+

To this:

+----------+---------+------+--------+
| order_id | line_no | type | config |
+----------+---------+------+--------+
|      100 |       1 | a    | 123    |
|      100 |       1 | d    | 456    |
|      100 |       1 | e    | X1     |
+----------+---------+------+--------+

Answer 1

In SQL Server 2000 there are not so many options available. You can use UNION ALL for this:

SELECT 'a' AS type, a
FROM mytable
WHERE a IS NOT NULL AND a <> 'NA'

UNION ALL 

SELECT 'b' AS type, b
FROM mytable
WHERE b IS NOT NULL AND b <> 'NA'

UNION ALL 

SELECT 'c' AS type, c
FROM mytable
WHERE c IS NOT NULL AND c <> 'NA'

UNION ALL 

SELECT 'd' AS type, d
FROM mytable
WHERE d IS NOT NULL AND d <> 'NA'