I read this SQL Server : Columns to Rows. How can I make sure that the order in which the rows appear in the result will be the same as the order of the columns?
As I mentioned in the comments, with an ORDER BY
. If you're using the UNPIVOT
operator, you may find this difficult if the names of the columns alphabetically don't match the ordinal positions. For example, if you have the following columns in the following order Surname
, FirstName
, PreferredColour
, then ordering the columns by name would get you FirstName
, PreferredColour
, Surname
.
As UNPIVOT
is pretty restrictive (like PIVOT
), you can instead use aVALUES
table construct. This makes it much easier, as you can add a column to the construct to ORDER BY
:
SELECT YT.KeyColumn,
UP.ColumnName,
UP.ColumnValue
FROM dbo.YourTable YT
CROSS APPLY(VALUES(1,N'Surname',YT.Surname),
(2,N'FirstName',YT.FirstName),
(3,N'PerferredColour',YT.PreferredColour))UP(Ordinal,ColumnName,ColumnValue)
ORDER BY YT.KeyColumn,
UP.Ordinal;