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.
CodePudding user response:
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.