Non-Aggregate Pivot

Question

I have a table like this:

╔════════╦═══╦═══╦═══╦═══╦═══╗
║ row_id ║ 1 ║ 2 ║ 3 ║ 4 ║ 5 ║
╠════════╬═══╬═══╬═══╬═══╬═══╣
║      1 ║ T ║ E ║ S ║ N ║ U ║
║      2 ║ M ║ B ║ R ║ H ║ A ║
║      3 ║ C ║ D ║ F ║ G ║ I ║
║      4 ║ J ║ K ║ L ║ O ║ P ║
║      5 ║ V ║ W ║ X ║ Y ║ Z ║
╚════════╩═══╩═══╩═══╩═══╩═══╝

I want to "pivot" the table to get an outcome where the row_id column is the first row, the 1 column the second etc.
The results should look like this:

╔════════╦═══╦═══╦═══╦═══╦═══╗
║ row_id ║ 1 ║ 2 ║ 3 ║ 4 ║ 5 ║
╠════════╬═══╬═══╬═══╬═══╬═══╣
║      1 ║ T ║ M ║ C ║ J ║ V ║
║      2 ║ E ║ B ║ D ║ K ║ W ║
║      3 ║ S ║ R ║ F ║ L ║ X ║
║      4 ║ N ║ H ║ G ║ O ║ Y ║
║      5 ║ U ║ A ║ I ║ P ║ Z ║
╚════════╩═══╩═══╩═══╩═══╩═══╝

I've looked for ideas about Pivoting without aggregates but without much luck, mainly since the data I want to pivot is non numeric.
I've set up the sample data in SQL Fiddle.

Thanks!

Can you edit to show how you need the final result? It's not exactly clear from your description. — Taryn, Aug 10 '14 at 12:51

score 1 · Answer 1 · answered Aug 10 '14 at 13:54

1

What you need is called "matrix transposition". The optimal SQL query will depend very much on the actual way you store the data, so it wouldn't hurt if you will provide more realistic example of your table' structure. Are you sure all matrices you will ever need to work with will be exactly 5*5 ? :)

UPD: Oh, I see you've found it.

answered Aug 10 '14 at 13:54

Roger Wolf

7,307
2
24
33

Actually, the answer is yes, it will always be 5*5. :-) – Gidil Aug 10 '14 at 13:58

score 0 · Answer 2 · edited May 23 '17 at 12:20

I realized my mistake was looking for pivot and not for transpose.

I found an answer here and solved the problem with the following query:

SELECT * 
FROM   (SELECT row_id, 
               col, 
               value 
        FROM   table1 
               UNPIVOT ( value 
                       FOR col IN ([1], 
                                   [2], 
                                   [3], 
                                   [4], 
                                   [5]) ) unpiv) src 
       PIVOT ( Max(value) 
             FOR row_id IN ([1], 
                            [2], 
                            [3], 
                            [4], 
                            [5]) ) piv

The results are on SQL Fiddle.

Non-Aggregate Pivot

2 Answers2