Is there a function for multiple column UNPIVOT without a matching WHERE clause in SSMS?

Question

I have a dataset that looks similar to below:

ID Key	Indicator 1	Indicator 1 Value	Indicator 2	Indicator 2	Indicator 3	Indicator 3 Value	etc...
ID###	Apples	TRUE	Pears	FALSE	Oranges	null	etc...
ID###	Oranges	TRUE	Pears	FALSE	APPLES	null	etc...
ID###	PEARS	TRUE	APPLES	FALSE	ORANGES	null	etc...

I've been able to unpivot the indicator names successfully into a single column however my difficulty is matching the indicator Value appropriately to the Indicator Name. Is there a WHERE statement that allows me to grep between the column names to unpivot or a similar operation that can help me sort this? The dataset in question comes from a XML set which may explain the abnormalities.

Answer 1

In SQL Server I'd just use APPLY

SELECT
  your_table.id_key,
  exploded.indicator,
  exploded.val
FROM
  your_table
CROSS APPLY
(
  VALUES
    (indicator_1, indicator_1_value),
    (indicator_2, indicator_2_value),
    ...
    (indicator_n, indicator_n_value)
)
  AS exploded(indicator, val)
WHERE
  exploded.indicator IS NOT NULL

Other DBMSs have similar functionality, such as lateral joins.