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

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'GOselect [1],[2],[3],[4],[5] -- for N columns, this goes to N*2-1from(    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-1pivot (max(value) for targetCol in ([1],[2],[3],[4],[5])) pvorder by targetRow

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

Shouldn't the table

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

rotated 45 degrees anti-clockwise be like this?

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

and the

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

something like:

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

There is no simple way of doing this directly in SQL.

I suggest you import the result into a different programming environment, such as Java, PHP, Python or what ever, solve the problem in this context, and then (if necessary) put the result back into the DB.