Should I use explode or Unpivot?

Question

I have a table consist of No Contract, Past, Current, and Expired columns.

|No.Cont| Past |Current|Expired|
+-------+------+-------+-------+
|113    |  X   |       |       |
|114    |      |   X   |       |
|115    |  X   |       |   X   |

I want to transform the table into this

|No.Cont| Category|
+-------+---------+
|113    |   Past  |
|114    | Current |
|115    |   Past  |
|115    | Expired |

I was told to create array [Past, Current, Expired], then explode it. But, I don't understand the steps.

I'd really appreciate any help.

Answer 1

You can use UNION ALL as follows:

SELECT T.NO, 'PAST' as Category FROM YOUR_TABLE WHERE T.PAST IS NOT NULL
UNION ALL
SELECT T.NO, 'CURRENT' FROM YOUR_TABLE WHERE T.CURRENT IS NOT NULL
UNION ALL
SELECT T.NO, 'EXPIRED' FROM YOUR_TABLE WHERE T.EXPIRED IS NOT NULL