Unpivot columns to rows

Question

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?

Answer 1

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;