Search code examples
sql-serverunpivotsql-server-2019

Unpivot columns to rows


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?


Solution

  • 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;