How can I transpose a query result in SQL?

Question

As the result of many subqueries, I have the following table:

+---------------------------------------------+
| register | simulation | hasLimit | approved |
+---------------------------------------------+
|     X    |     Y      |    Z     |    W     |
+---------------------------------------------+

But I want it to be like this:

+----------------+
| register   | X | 
+----------------+
| simulation | Y | 
+----------------+
| hasLimit   | Z | 
+----------------+
| approved   | W | 
+----------------+

In R I would simply transpose using the function t(), however in SQL it seems a bit harder. I've tried to understand the function pivot to apply in this case, but it seems a little strange, since I'm actually just transposing, not pivoting anything.

Tag your question with the DBMS you are using (SQL Server, Oracle, MySQL etc.) — Aaron Dietz, Jul 03 '19 at 18:47
Has already been answered here before: https://stackoverflow.com/questions/18026236/sql-server-columns-to-rows — B. Seberle, Jul 03 '19 at 18:49

score 0 · Answer 1 · answered Jul 03 '19 at 18:52

0

You seem to want an "unpivot" operation.

Assuming the types of the columns are all compatible, you can use union all:

select 'register' as which, register as value union all
select 'simulation',  simulation union all
select 'hasLimit', hasLimit union all
select 'approved', approved;

Some databases require a FROM clause for each subquery, such as from dual.

This is a very small amount of data. For larger data, I prefer a lateral join, but the syntax (and availability of which) depends on the database.

answered Jul 03 '19 at 18:52

Gordon Linoff

1,242,037
58
646
786

The table that I present in the beginning is not a table itself, is the result of a lot of queries side by side, that I put all together in a single query, and each individual query gives me a certain number (X, Y, Z, W). I think I have to use the unpivot function, but I couldn't find how to do that INSIDE the query that generates this result – Eduarda Oliveira Jul 03 '19 at 19:11
1

@EduardaOliveira to be able to use unpivoting we need to make sure your DBMS product is suitable for it. i.e. you need to tell your DBMS firsty. – Barbaros Özhan Jul 03 '19 at 19:25

score 0 · Answer 2 · answered Jul 03 '19 at 19:00

Given the return from

         SELECT 'x' AS register
              , 'y' AS simulation
              , 'z' AS hasLimit
              , 'w' AS approved

We can wrap that in a set of parens and reference it as an inline view in an outer query.

For example:

SELECT CASE i.n
         WHEN 1 THEN 'register'
         WHEN 2 THEN 'simulation'
         WHEN 3 THEN 'hasLimit'
         WHEN 4 THEN 'approved'
       END AS col1
     , CASE i.n
         WHEN 1 THEN q.register
         WHEN 2 THEN q.simulation
         WHEN 3 THEN q.hasLimit
         WHEN 4 THEN q.approved
       END AS col2
  FROM (
         SELECT 'x' AS register
              , 'y' AS simulation
              , 'z' AS hasLimit
              , 'w' AS approved
       ) q
 CROSS
  JOIN ( SELECT 1 AS n
         UNION ALL SELECT 2
         UNION ALL SELECT 3
         UNION ALL SELECT 4
       ) i
 ORDER
    BY i.n

That seems a bit of a rigmarole, given that you already have subqueries that are returning scalar values, we could combine those with UNION ALL set operators

SELECT 'register'  AS col1, ( scalar_subquery_for_x ) AS col2
UNION ALL 
SELECT 'simulation'       , ( scalar_subquery_for_y )
UNION ALL 
SELECT 'hasLimit'         , ( scalar_subquery_for_z )
UNION ALL 
SELECT 'approved'         , ( scalar_subquery_for_w )

IF we need a guaranteed order, we can wrap that whole thing in parens and add an ORDER BY clause. (Without the ORDER BY, we do observe rows are returned "in order" from the UNION ALL, but this behavior is not guaranteed.)

score 0 · Answer 3 · answered Jul 04 '19 at 03:23

0

Use a simple UNPIVOT as below,

SELECT Col, Val
FROM <Your Table>
UNPIVOT (Val FOR Col IN([register],[simulation],[hasLimit],[approved])) unpiv

answered Jul 04 '19 at 03:23

MJoy

1,349
2
9
23

How can I transpose a query result in SQL?

3 Answers3