What is the ANSI standard SQL equivalent of UNPIVOT?

In T-SQL, PIVOT is redundant because you can always replace it with a combination of CASE WHEN and GROUP BY. I presume that the same is true of UNPIVOT. If I had some T-SQL UNPIVOT code that I wanted to conform to the SQL standard (let's say, the current ANSI), what would I replace the UNPIVOT with? My best guess so far is some usage of UNION ALL?

A good answer would show a quick example of such a conversion.

Solution

For an UNPIVOT such as this:

SELECT u.*
FROM YourTable AS t
UNPIVOT (
    ColumnValue FOR ColumnName IN (
        Unpivot1, Unpivot2, Unpivot3
    )
) AS u;

You can do the same thing with a Lateral Join and a VALUES constructor

SELECT u.*
FROM YourTable AS t
CROSS JOIN LATERAL (VALUES
    ('UnPivot1', t.Unpivot1),
    ('UnPivot2', t.Unpivot2),
    ('UnPivot3', t.Unpivot3)
) AS u(ColumnName, ColumnValue);

In SQL Server this can be done using CROSS APPLY

SELECT u.*
FROM YourTable AS t
CROSS APPLY (VALUES
    ('UnPivot1', t.Unpivot1),
    ('UnPivot2', t.Unpivot2),
    ('UnPivot3', t.Unpivot3)
) AS u(ColumnName, ColumnValue);

I normally recommend this method even in SQL Server, as it's much more flexible. For example, you can unpivot multiple columns at a time.

Exactly which product you are using that supports ANSI-SQL perfectly is a different question, I'm not aware of any that do. you are best off just using the syntax that your product provides.