Question: I have a customers table and an orders table. I want to display the customer and all of his/her order dates on one row, rather than multiple rows. Here is what I have and what I'm looking for:

![Picture of both tables, current results, what i'm looking for](https://i.stack.imgur.com/IQclF.png)

Basic code to get results:

```
select customerid, name, orderdate
from customer_table c inner join
     order_table o
     on c.customerid = o.customerid 

```

Answer: 
Like others said, it's in principle impossible (in pure SQL) to generate this not knowing how many orders you have for a single customer.

I like @nikhil-sugandh's answer, it works great if you are OK with having all orders comma-separated in a single column.

If you insist on having multiple columns, you can build on that answer, by replacing `LISTAGG` with `ARRAY_AGG` and postprocessing it. It will be MUCH more efficient than e.g. the proposed solution with multiple joins. You can also use `ARRAY_SLICE` to handle cases when there are more orders than you are prepared for.

Example (note, I added an extra order to demonstrate handling more-than-expected orders

```
create or replace table customer_table(customerId int, name varchar) 
as select * from values
(12,'John'),(4,'Nancy');

create or replace table order_table(orderId int, customerId int, orderDate date) 
as select * from values
(1,12,'3/4/2017'),(2,12,'5/26/2017'),(3,12,'12/1/2017'),(4,4,'2/1/2017'),(5,12,'1/1/2019');

with subq as (
  select c.customerid, name, 
    array_agg(orderdate) within group (order by orderdate) as orders
  from customer_table c 
  inner join order_table o on c.customerid = o.customerid 
  group by c.customerid, c.name
) 
select customerid, name, 
  orders[0]::date AS order1, orders[1]::date AS order2,
  array_to_string(array_slice(orders, 2, 999), ' , ') AS overflow 
from subq;

------------+-------+------------+------------+-------------------------+
 CUSTOMERID | NAME  |   ORDER1   |   ORDER2   |        OVERFLOW         |
------------+-------+------------+------------+-------------------------+
 4          | Nancy | 2017-02-01 | [NULL]     |                         |
 12         | John  | 2017-03-04 | 2017-05-26 | 2017-12-01 , 2019-01-01 |
------------+-------+------------+------------+-------------------------+

```
first create a view like this:

```
create view order_view as 
select 
count(*) over (partition by customerId order by orderDate) as ord,
CustomerId,
orderdate
 from order_table 

```

then you can use this query:

```
 select c.customerid, 
 o1.orderdate,
 o2.orderdate
 o3.orderdate
 .
 .
 .
 o12.orderdate
from customer_table c 
left join  order_view  o1
     on c.customerid = o1.customerid and ord = 1 
left join  order_view  o2
     on c.customerid = o2.customerid and ord = 2 
left join  order_view  o3
     on c.customerid = o3.customerid and ord = 3
.
.
.     
left join  order_view  o12
     on c.customerid = o12.customerid and ord = 12

```