One thing you forgot is to keep NULL in mind, unless we change the original schema to not allow nulls. Also, how this suggestion will look like if we put more columns in the table, let us say 10 columns?

As I said, your suggestion is ingenious, so do not take me wrong for what I said. If we insert another row and one of the columns is null, then it will fail to do what it was intended to, but that is something you can fix with no much effort.

The suggestion I sent is very slow compare with yours. The only thing I would point as a pro, is that you can use it for any number of columns, the change will not be much and we could also tweak it to be dynamic. The approach is not complicated, and this is what it does.

- Assign a subrrogate key using row_number function. We could get rid of it if we had one, anyway every relational table should have a primary key.

- Pivot the data

- Assign a row number to each value (column), partitioned by the subrrogate key, in order to sort the columns that now are rows after the pivoting.

- Pivot the data back

Let us suppose that we do have a primary key and that all columns are not nullable. Then we could use something like:

Sorry for the format, but I do not know how to change this editor to work like SSMS.

As you can see, if we have 10 or 20 columns, we can easely change the query. Now, if we have NULL values, then the previous query will fail, because the UNPIVOT operator will not create a row for NULL values, so we will be missing those and you will notice it after we pivot the data to obtain same schema as the original. That tell us that we can not unpivot the data using the UNPIVOT operator, so I decided to use a cross join with a table having same rows as columns in the original table.

WITH unpvtAS(SELECT pk, [value], ROW_NUMBER() OVER(PARTITION BY pk ORDER BY [value]) AS rnFROM ( SELECT pk, CASE n.c1 WHEN 1 THEN t.c1 WHEN 2 THEN t.c2 WHEN 3 THEN t.c3 END AS [value] FROM @t AS t CROSS JOIN (SELECT 1 AS c1 UNION ALL SELECT 2 UNION ALL SELECT 3) AS n ) AS r)SELECT DISTINCT [1] AS c1, [2] AS c2, [3] AS c3FROM unpvt PIVOT ( MIN([value]) FOR rn IN ([1], [2], [3]) ) AS pvtORDER BY c1, c2, c3;

Last, because we do not have a primary key, I had to use a subrrogate one.

WITH unpvtAS(SELECT pk, [value], ROW_NUMBER() OVER(PARTITION BY pk ORDER BY [value]) AS rnFROM ( SELECT pk, CASE n.c1 WHEN 1 THEN t.c1 WHEN 2 THEN t.c2 WHEN 3 THEN t.c3 END AS [value] FROM (SELECT c1, c2, c3, ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS pk FROM @t) AS t CROSS JOIN (SELECT 1 AS c1 UNION ALL SELECT 2 UNION ALL SELECT 3) AS n ) AS r)SELECT DISTINCT [1] AS c1, [2] AS c2, [3] AS c3FROM unpvt PIVOT ( MIN([value]) FOR rn IN ([1], [2], [3]) ) AS pvtORDER BY c1, c2, c3;

As you see now, the performance is far from the performance of your suggestion, because yours need to scan the table just one.