Блог

У меня есть таблица клиентов и таблица заказов. Я хочу отобразить клиента и все даты его / ее заказа в одной строке, а не в нескольких строках. Вот что у меня есть и что я ищу:

Базовый код для получения результатов:

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

это будет работать максимум, если вы не сможете отобразить его в разных столбцах с нулевыми значениями:

 select customer_id,name,LISTAGG(orderdate, ', ') WITHIN GROUP (ORDER BY orderdate) 
from(select customerid, name, orderdate
from customer_table c inner join
order_table o
on c.customerid = o.customerid );
  

Вы можете использовать следующее, поскольку вы ограничены лимитом в 12 заказов. Если вы расширяетесь до неизвестного количества заказов без верхнего предела, то вам нужно будет использовать динамический SQL, и даже тогда это будет сложно, потому что вам также потребуется динамически создавать уникальные имена столбцов.

Этот запрос распределяется по заказчикам и задает значения заказа. Они будут находиться в датированном порядке и будут иметь значение NULL, если заказов больше не будет. Это как бы предполагает, что по крайней мере у одного клиента есть 12 заказов. Вы получите столбец всех нулей, если это не так

 IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
IF OBJECT_ID('tempdb..#sortedRows') IS NOT NULL DROP TABLE #SortedRows
DECLARE @CustomerList TABLE(CustomerID INT, RowNo INT); 
INSERT INTO @CustomerList SELECT DISTINCT CustomerID, ROW_NUMBER() OVER(ORDER BY CustomerID) RowNo FROM Customer_Table (NOLOCK)
DECLARE @Count INT = (SELECT COUNT(DISTINCT CustomerID) RowNumber FROM @CustomerList)
DECLARE @Counter INT = 0
DECLARE @CustToProcess INT
CREATE TABLE #Results(CustomerID INT, [Name] VARCHAR(50), OrderDate1 DATETIME, 
OrderDate2 DATETIME, OrderDate3 DATETIME, OrderDate4 DATETIME, OrderDate5 DATETIME, 
OrderDate6 DATETIME, OrderDate7 DATETIME, OrderDate8 DATETIME, OrderDate9 DATETIME, 
OrderDate10 DATETIME, OrderDate11 DATETIME, OrderDate12 DATETIME) 
INSERT INTO #Results(CustomerID, Name) SELECT DISTINCT CustomerID, Name FROM Customer_Table

SELECT ROW_NUMBER() OVER(PARTITION BY c.CustomerID ORDER BY OrderDate) RowNo,
c.CustomerID, c.Name, t.OrderDate  INTO #SortedRows
FROM Customer_Table c (NOLOCK) JOIN Order_Table t ON c.CustomerID = t.CustomerID 

WHILE @Counter < @Count
BEGIN
    SET @Counter  = 1
    SET @CustToProcess = (SELECT CustomerID FROM @CustomerList WHERE RowNo = @Counter)
    PRINT @CustToProcess
    SELECT * INTO #RowsForProcessing FROM #SortedRows WHERE CustomerID = @CustToProcess
    UPDATE #Results SET OrderDate1 = (SELECT OrderDate FROM #RowsForProcessing WHERE Rowno = 1) WHERE CustomerID = @CustToProcess
    UPDATE #Results SET OrderDate2 = (SELECT OrderDate FROM #RowsForProcessing WHERE Rowno = 2) WHERE CustomerID = @CustToProcess
    UPDATE #Results SET OrderDate3 = (SELECT OrderDate FROM #RowsForProcessing WHERE Rowno = 3) WHERE CustomerID = @CustToProcess
    UPDATE #Results SET OrderDate4 = (SELECT OrderDate FROM #RowsForProcessing WHERE Rowno = 4) WHERE CustomerID = @CustToProcess
    UPDATE #Results SET OrderDate5 = (SELECT OrderDate FROM #RowsForProcessing WHERE Rowno = 5) WHERE CustomerID = @CustToProcess
    UPDATE #Results SET OrderDate6 = (SELECT OrderDate FROM #RowsForProcessing WHERE Rowno = 6) WHERE CustomerID = @CustToProcess
    UPDATE #Results SET OrderDate7 = (SELECT OrderDate FROM #RowsForProcessing WHERE Rowno = 7) WHERE CustomerID = @CustToProcess
    UPDATE #Results SET OrderDate8 = (SELECT OrderDate FROM #RowsForProcessing WHERE Rowno = 8) WHERE CustomerID = @CustToProcess
    UPDATE #Results SET OrderDate9 = (SELECT OrderDate FROM #RowsForProcessing WHERE Rowno = 9) WHERE CustomerID = @CustToProcess
    UPDATE #Results SET OrderDate10 = (SELECT OrderDate FROM #RowsForProcessing WHERE Rowno = 10) WHERE CustomerID = @CustToProcess
    UPDATE #Results SET OrderDate11 = (SELECT OrderDate FROM #RowsForProcessing WHERE Rowno = 11) WHERE CustomerID = @CustToProcess
    UPDATE #Results SET OrderDate12 = (SELECT OrderDate FROM #RowsForProcessing WHERE Rowno = 12) WHERE CustomerID = @CustToProcess

    DROP Table #RowsForProcessing
END
SELECT * FROM #Results
  

Попробуйте это, если вы используете 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)
  

Пожалуйста, измените имя столбца. Я использовал cid в качестве вашего customerid.

Вывод:

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

Как говорили другие, в принципе невозможно (в чистом SQL) сгенерировать это, не зная, сколько заказов у вас есть для одного клиента.

Мне нравится ответ @nikhil-sugandh, он отлично работает, если вы согласны с тем, что все заказы разделены запятыми в одном столбце.

Если вы настаиваете на наличии нескольких столбцов, вы можете использовать этот ответ, заменив LISTAGG его на ARRAY_AGG и обработав его. Это будет НАМНОГО эффективнее, чем, например, предлагаемое решение с несколькими объединениями. Вы также можете использовать ARRAY_SLICE для обработки случаев, когда заказов больше, чем вы готовы.

Пример (обратите внимание, я добавил дополнительный заказ, чтобы продемонстрировать обработку большего количества заказов, чем ожидалось

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

сначала создайте представление, подобное этому:

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

затем вы можете использовать этот запрос:

  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