时间戳

首页 > 解决方案 > 将多个订单行合并为每个订单 1 行，最多 5 个

sql - 将多个订单行合并为每个订单 1 行，最多 5 个

问题描述

使用 SQL Server 2016

问题：我从我们的网络订购系统收到一个平面文件。我需要将此数据导入我们的内部邮购系统。Web 订单文件包含每个订单每个项目 1 行。因此，如果订购了 12 件商品，则将有 12 行具有相同的订单 ID。我需要对此进行转换，以便订单项位于同一行。需要注意的是，每个订单每行最多只能有 5 个订单项。所以订购的 12 件商品需要变成 3 行。在第 1 行和第 2 行的末尾，需要有一个继续标志，说明订单在下一行继续。

网络订单文件示例：

网络订单样本

SAMPLE# Row orderid ocustomerid odate   ofirstname  customer_pmntprofileid itemid   numitems    unitprice   itemamount
1   1   171476  5230    11/14/2018  Scott   1324426745  R22046  1   269.95  269.95
1   2   171476  5230    11/14/2018  Scott   1324426745  R81100  12  7.5 90
3   1   171690  6119    11/14/2018  Joe 1382758196  AF49972 6   39.95   239.7
3   2   171690  6119    11/14/2018  Joe 1382758196  AF49973 4   39.95   159.8
3   3   171690  6119    11/14/2018  Joe 1382758196  AF47970 1   43.95   43.95
3   4   171690  6119    11/14/2018  Joe 1382758196  AF44090 1   43.95   43.95
3   5   171690  6119    11/14/2018  Joe 1382758196  AF44091 1   43.95   43.95
3   6   171690  6119    11/14/2018  Joe 1382758196  AF44092 1   43.95   43.95
3   7   171690  6119    11/14/2018  Joe 1382758196  AF44093 1   43.95   43.95
3   8   171690  6119    11/14/2018  Joe 1382758196  AF44094 1   43.95   43.95
3   9   171690  6119    11/14/2018  Joe 1382758196  AF44114 1   50.95   50.95
3   10  171690  6119    11/14/2018  Joe 1382758196  AF44139 1   46.95   46.95
3   11  171690  6119    11/14/2018  Joe 1382758196  AF44138 1   46.95   46.95
3   12  171690  6119    11/14/2018  Joe 1382758196  AF44066 1   43.95   43.95
4   1   171702  6120    11/14/2018  Tim 1382791806  L81313  5   17.95   89.75
4   2   171702  6120    11/14/2018  Tim 1382791806  L81314  1   30.95   30.95
4   3   171702  6120    11/14/2018  Tim 1382791806  L82039  6   8.95    53.7
4   4   171702  6120    11/14/2018  Tim 1382791806  L82013  1   69.95   69.95

我需要将样品 1 变成 1 行，样品 3 变成 3 行，样品 4 变成 1 行。需要的输出：

订购进口样品

SAMPLE #    orderid ocustomerid odate   ofirstname  customer_pmntprofileid  ITEM_ID1    QTY1    PRICE1  AMOUNT1 ITEM_ID2    QTY2    PRICE2  AMOUNT2 ITEM_ID3    QTY3    PRICE3  AMOUNT3 ITEM_ID4    QTY4    PRICE4  AMOUNT4 ITEM_ID5    QTY5    PRICE5  AMOUNT5 Continue
1   171476  5230    11/14/2018  Scott   1324426745  R22046  1   269.95  269.95  R81100  12  7.5 90                                                  N
3   171690  6119    11/14/2018  Joe 1382758196  AF49972 6   39.95   239.7   AF49973 4   39.95   159.8   AF47970 1   43.95   43.95   AF44090 1   43.95   43.95   AF44091 1   43.95   43.95   Y
3   171690  6119    11/14/2018  Joe 1382758196  AF44092 1   43.95   43.95   AF44093 1   43.95   43.95   AF44094 1   43.95   43.95   AF44114 1   50.95   50.95   AF44139 1   46.95   46.95   Y
3   171690  6119    11/14/2018  Joe 1382758196  AF44138 1   46.95   46.95   AF44066 1   43.95   43.95                                                   N
4   171702  6120    11/14/2018  Tim 1382791806  L81313  5   17.95   89.75   L81314  1   30.95   30.95   L82039  6   8.95    53.7    L82013  1   69.95   69.95                   N
5   171706  70  11/14/2018  Bruce   1382821080  L84264  1   584.99  584.99  L84409  1   584.99  584.99  L84410  1   584.99  584.99  L84633  1   539.99  539.99  L84634  1   539.99  539.99  Y

标签： sqlsql-servertsql

解决方案

你可以这样做：

WITH 
t1 AS (select * FROM mytable WHERE row % 5 = 1),
t2 AS (select * FROM mytable WHERE row % 5 = 2),
t3 AS (select * FROM mytable WHERE row % 5 = 3),
t4 AS (select * FROM mytable WHERE row % 5 = 4),
t5 AS (select * FROM mytable WHERE row % 5 = 0)
SELECT t1.SAMPLE,
       ceiling(t1.Row/5.0) AS Row,
       t1.orderid,
       t1.ocustomerid,
       t1.odate,
       t1.ofirstname,
       t1.customer_pmntprofileid,
       t1.itemid AS itemid1,
       t1.numitems AS numitems1,
       t1.unitprice AS unitprice1,
       t1.itemamount AS itemAmount1,
       t2.itemid AS itemId2,
       t2.numitems AS numitems2,
       t2.unitprice AS unitprice2,
       t2.itemamount AS itemamount2,
       t3.itemid AS itemId3,
       t3.numitems AS numitems3,
       t3.unitprice AS unitprice3,
       t3.itemamount AS itemamount3,
       t4.itemid AS itemId4,
       t4.numitems AS numitems4,
       t4.unitprice AS unitprice4,
       t4.itemamount AS itemamount4,
       t5.itemid AS itemId5,
       t5.numitems AS numitems5,
       t5.unitprice AS unitprice5,
       t5.itemamount AS itemamount5
FROM t1
LEFT JOIN t2 ON t1.orderid = t2.orderid AND CEILING(t1.row/5.0)=ceiling(t2.row/5.0)
LEFT JOIN t3 ON t1.orderid = t3.orderid AND CEILING(t1.row/5.0)=ceiling(t3.row/5.0)
LEFT JOIN t4 ON t1.orderid = t4.orderid AND CEILING(t1.row/5.0)=ceiling(t4.row/5.0)
LEFT JOIN t5 ON t1.orderid = t5.orderid AND CEILING(t1.row/5.0)=ceiling(t5.row/5.0);

DBFiddle 演示

推荐阅读