sqlpostgresqlselect

How to create column and rows using select statement CTE?


I'm able to create the table using CTE with select statement like below but it's not behaving like I want.

with sorting(item_code, index) as (
 select (10001, 1), (10002, 2)
)
select * from sorting

Table result :

item_code|index    |
---------+---------+
(10001,1)|(10002,2)|

Basically I want to create table with value like this :

item_code | index
10001     | 1
10002     | 2

How could I do that using SQL?


Solution

  • This is most probably what you want using the values clause:
    demo at db<>fiddle

    with sorting(item_code, index)as(values
      (10001, 1)
     ,(10002, 2) )
    select * from sorting;
    
    item_code index
    10001 1
    10002 2