convert data from multiple columns into single row sorting descending-CodePudding

I am trying to query the original source which contain totals from a category (in this case Vehicles) into the second table.

Motorcycle	Bicycle	Car
1	3	2

Desired Output:

Vehicle	Quantity
Bicycle	3
Car	2
Motorcycle	1

Additionally, I need that the Quantity is sorted in descending order like showing above.

So far I have tried to do an Unpivot, but there is a syntax error in the Unpivot function. Is there another way to reach out the same results?

My code so far:

SELECT Vehicle_Name 
FROM 
( 
SELECT [Motorcycle], [Bycycle], [Car] from Data
) as Source  
UNPIVOT 
( 
Vehicle FOR Vehicle_Name IN ([Motorcycle], [Bycycle], [Car])
) as Unpvt

CodePudding user response：

Try this

with data1 as 
(
Select * from data)
Select * From 
(
Select 'motorcycle' as "Vehicle", motorcycle  as quantity from data1  
union all 
Select 'bicycle' , bicycle from data1
union all 
Select 'car', car from data1
)  order by quantity desc;

CodePudding user response：

Since we don't know what DBMS, here's a way that'd work in the one I use the most.

SELECT *
FROM (SELECT map_from_entries(
                   ARRAY[('Motorcycle', Motorcycle),
                         ('Bicycle', Bicycle),
                         ('Car', Car)])
        FROM Source) AS t1(type_quant)
CROSS JOIN UNNEST(type_quant) AS t2(Vehicle, Quantity)
ORDER BY Quantity DESC

-Trino

CodePudding user response：

Edit: Added sort requirement.

You can use CROSS APPLY here too

select vehicle, amnt
from test
cross apply( 
 VALUES('motorcycle', motorcycle)
   ,('bicycle', bicycle)
   ,('car', car)) x (vehicle, amnt)
order by amnt desc

Fiddle here