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?

May I know what is the purpose of rotating the table values to 45 degrees? — RGS, May 11 '16 at 10:34

score 68 · Accepted Answer · answered Feb 26 '11 at 21:30

68

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.

answered Feb 26 '11 at 21:30

RichardTheKiwi

105,798
26
196
262

6

Thanks Richard, you are really a SQL Guru! – Just a learner Feb 27 '11 at 16:09
14

This is one of the smartest things i've seen on SO. Even for purely theoretical value, i would like to see this in a blog explained and generalized for multiples of 45 degrees. Brilliant Richard. Sorry if i sound like a fan boy, but i am really amazed by this:) – Radu Caprescu Feb 27 '11 at 21:09
2

To get ypercube's output, change `targetCol = ROW_NUMBER()..` to `targetCol = ABS(3 - (row+col-1))-1+2*ROW_NUMBER()..` I'll add more comments if inspiration avails to expand on the answer – RichardTheKiwi Mar 02 '11 at 19:18
4

This is ridiculous and amazing. – Ben Brocka Jan 19 '12 at 22:43
Checking someone's profile when discussing some issues can be enlightening, sometimes great things are found. Dude, this piece of code is amazing. I wonder if @Yousui finally got done his SQL chess game, it would be great to take a look and learn from it. – Yaroslav Nov 30 '12 at 09:48
A magnificent answer, its upvotes only limited by the fact that no sane person on earth would actually want to do this :) – Matt Gibson Sep 03 '15 at 13:39

score 9 · Answer 2 · answered Feb 26 '11 at 12:23

9

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

answered Feb 26 '11 at 12:23

ypercubeᵀᴹ

113,259
19
174
235

24

No, but I couldn't paste these tables inside a comment. We deserve a clear description of the problem before trying to answer, don't we? – ypercubeᵀᴹ Feb 26 '11 at 13:09
5

ypercube - one more step (flush to left) and you will have the question structure – RichardTheKiwi Feb 26 '11 at 21:16
@Richard: I see the light now, thnx! – ypercubeᵀᴹ Feb 27 '11 at 07:13
4

But this way (as suggested by OP) leaves the problem that rotating 8 times by 45 degrees, will not get you back to the original position (table). – ypercubeᵀᴹ Feb 27 '11 at 07:15
3

yeah, the term "rotation" was ill-chosen – Martin DeMello Feb 27 '11 at 13:16
You can explain it in your question above separating by `
`. – Naveed Mar 02 '11 at 04:45

score 3 · Answer 3 · answered Feb 26 '11 at 11:28

3

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.

answered Feb 26 '11 at 11:28

aioobe

413,195
112
811
826

Whoosh... the sound of the point of all of this flying over someone's head at high speed. It's the same reason people do stuff like [this](https://www.ioccc.org/)! – Vérace Dec 02 '19 at 15:08

Aleksandr Fedorenko · Answer 4 · 2013-04-06T06:37:00.203

Option for SQLServer2008+ with CROSS APPLY and PIVOT operators

CREATE TABLE dbo.test77
 (
  id int IDENTITY, 
  colA char(1), 
  colB char(1), 
  colC char(1)
  )

INSERT dbo.test77
VALUES('a','b','c'),
      ('d','e','f'),
      ('g','h','i')

SELECT [1], [2], [3], [4], [5]
FROM (
      SELECT COALESCE(o.colA, o.colB, o.colC) AS Val,
             'Col' + CAST(ROW_NUMBER() OVER (ORDER BY id) AS nvarchar(1)) AS ColName 
      FROM dbo.test77 t CROSS APPLY (
                                     VALUES(colA, NULL, NULL),
                                           (NULL, colB, NULL),
                                           (NULL, NULL, colC)
                                     ) o(colA, colB, colC)
      ) p
PIVOT (
MAX(Val) FOR ColName IN ([Col1], [Col2], [Col3], [Col4], [Col5], [Col6], [Col7], [Col8], [Col9])
) pvt CROSS APPLY (
                   VALUES ([Col3], NULL, NULL, NULL, NULL),
                          ([Col2], [Col6], NULL, NULL, NULL),
                          ([Col1], [Col5], [Col9], NULL, NULL),
                          ([Col4], [Col8], NULL, NULL, NULL),
                          ([Col7], NULL, NULL, NULL, NULL)
                   ) o([1], [2], [3], [4], [5])

Demo on SQLFiddle

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

4 Answers4