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?

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.

Answer 2

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