sql - How to rotate a table 45 degrees and save the result into another table?

Question

I have a table.

---------
| a | b |
---------
| a | b |
---------

I want to rotate it 45 degrees(clockwise or anti-clockwise) and save it into another table. For example, if I rotate it 45 degrees anti-clockwise, it will be:

-------------
| b |   |   |
-------------
| a | b |   |
-------------
| a |   |   |
-------------

Another example, when I rotate

-------------
| a | b | c |
-------------
| d | e | f |
-------------
| g | h | i |
-------------

It will change to

---------------------
| c |   |   |   |   |
---------------------
| b | f |   |   |   |
---------------------
| a | e | i |   |   |
---------------------
| d | h |   |   |   |
---------------------
| g |   |   |   |   |
---------------------

How to do this in SQL?

question from:https://stackoverflow.com/questions/5126592/how-to-rotate-a-table-45-degrees-and-save-the-result-into-another-table

Answer 1

A fully working example (for SQL Server 2005+)
If you need it for another system, there are equivalents for the pieces of the puzzle below

• row_number()
• dense_rank()
• un/pivot

You can find the equivalents from other Stackoverflow questions. For example, the first two are well supported by Oracle and DB2.

create table t45 (id int identity, colA char(1), colX char(1), colZ char(1))
insert t45 select 'a','b','c'
insert t45 select 'd','e','f'
insert t45 select 'g','h','i'
GO

select [1],[2],[3],[4],[5] -- for N columns, this goes to N*2-1
from
(
    select value,
        targetRow = row+col-1,
        targetCol = ROW_NUMBER() over (partition by row+col-1 order by row)
    from
    (
        select *,
            row = DENSE_RANK() over (order by id),
            col = ROW_NUMBER() over (partition by id order by
                CASE source when 'colA' then 3 -- number in reverse
                            when 'colX' then 2
                            when 'colZ' then 1 end)
        from t45
        unpivot (value for source in (colA,colX,colZ)) upv
    ) x
) p                                -- for N columns, this goes to N*2-1
pivot (max(value) for targetCol in ([1],[2],[3],[4],[5])) pv
order by targetRow

If you need to arbitrarily apply it to any table - use dynamic SQL to generate the pattern shown above.