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: 
Try this if you use MS SQL Server:

```
 -- 1st, get the number of columns
declare @columnnumber int = 1
select @columnnumber = max(a.count)
from (
select c.cid,c.name, count(o.orderdate) as count
from 
customer_table c
join order_table o
on c.cid = o.cid
group by c.cid,c.name)a
print @columnnumber

-- Compose the column names for Pivot
declare @columnname varchar(max) = ''
declare @int int = 1
while @int <= @columnnumber
begin
set @columnname = @columnname + '[date' + cast(@int as varchar(10))+ '],'
set @int = @int + 1
end
set @columnname = '('+left(@columnname,len(@columnname)-1)+')'
print @columnname

--Pivot !!! + Dynamic SQL
declare @str varchar(max)
set @str = 
'SELECT *  
FROM  
(SELECT c.cid,c.name, o.orderdate,concat(''date'',row_number() over (partition by c.cid,c.name order by o.orderdate)) rnk
    FROM customer_table c
    join order_table  o
    on c.cid = o.cid) AS s
PIVOT  
(  
min(s.orderdate)  
FOR s.rnk IN '+ @columnname+
' ) AS PivotTable'
print @str

execute (@str)

```

Please change the column name. I used cid as your customerid.

Output:

```
cid name    date1       date2       date3 
12  John    2017-03-04  2017-05-26  2017-12-01
4   Nancy   2017-02-01  NULL        NULL

```
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

```