In sql server, how do i group rows that have same values in a select query?

I have the following sql query:

我有以下sql查询:

SELECT pd.trackingno                                                AS [Parcel],
         Trim(o.orderno) + '_'
         + CONVERT(NVARCHAR, pd.groupnum )                            AS [Order],
         Isnull(vi.[gtin], '''')                                      AS [Item],
         Isnull(i.skuno, '')                                          AS [Article],
         Isnull(ix_clr.skuno, '')                                     AS [Color],
         Isnull(ix_sze.skuno, '')                                     AS [Size],
         Isnull(i.[description], '')                                  AS [Description],
         CONVERT(DECIMAL(8, 2), oi.unitprice * oi.quantity)           AS [Price],
         CONVERT(INT, oi.quantity)                                    AS [Sent]
  FROM   (SELECT orderno,
                 'S' Type,
                 groupnum
          FROM   shipments
          WHERE  shipbatch BETWEEN '2018-04-26 00:00:00' AND '2018-04-27 00:00:00') ords
         INNER JOIN orders AS o
                 ON o.orderno = ords.orderno
         INNER JOIN packageTable AS pd
                 ON ords.orderno = pd.orderno
                    AND ords.groupnum = pd.groupnum
         INNER JOIN oiTable oi
                 ON oi.orderno = ords.orderno
                    AND Isnull(ords.groupnum, 0) = Isnull(oi.groupnum, 0)
         INNER JOIN detailsTable AS od
                 ON ords.orderno = od.orderno
                    AND oi.linenum = od.linenum
         INNER JOIN itemsTable i
                 ON od.itemid = i.itemid
         left JOIN itemXref ix_clr
                 ON i.itemid = ix_clr.itemid
                    AND ix_clr.skutype = 'CLR'
         left JOIN itemXref ix_sze
                 ON i.itemid = ix_sze.itemid
                    AND ix_sze.skutype = 'SZE'
         INNER JOIN vItemTables vi
                 ON vi.itemid = od.itemid
  GROUP  BY pd.trackingno,
            o.orderno,
            pd.groupnum,
            vi.gtin,
            i.skuno,
            ix_clr.skuno,
            ix_sze.skuno,
            i.description,
            o.tax,
            oi.unitprice,
            oi.quantity

which generates the below output:

生成以下输出:

+---------------+-----------+----------------+----------+-------+--------+-------+------+
|    Parcel     |   Order   |      Item      |  Alias   | Color |  Size  | Price | Sent |
+---------------+-----------+----------------+----------+-------+--------+-------+------+
| DPV0010260188 | DHRU124_1 | 00717851968853 | 9E9D2256 | Red   | MEDIUM |  2.00 |    1 |
| DPV0010260188 | DHRU124_1 | 00717851968853 | 9E9D2256 | Red   | MEDIUM |     1 |    1 |
| DPV0010260188 | DHRU124_1 | 00021331918437 | 9782189D |       |        |     4 |    1 |
+---------------+-----------+----------------+----------+-------+--------+-------+------+

As you can see, except for the send field, the rest of the data is same. It need not be same all the time.Alias are basically items inside a package. So i want to group the rows together based on the alias value and sum up their price and sent value. So that it could be read as order and the number of same items and their total price. Attached screenshot has the table values for reference

如您所见,除了发送字段外,其余数据都是相同的。它不需要一直相同.Alias基本上是包内的物品。所以我想根据别名值将行分组在一起,并总结它们的价格和发送值。因此,它可以被视为订单和相同项目的数量及其总价格。附加的屏幕截图具有表值以供参考

Expected output:

+---------------+-----------+----------------+----------+-------+--------+-------+------+
|    Parcel     |   Order   |      Item      |  Alias   | Color |  Size  | Price | Sent |
+---------------+-----------+----------------+----------+-------+--------+-------+------+
| DPV0010260188 | DHRU124_1 | 00717851968853 | 9E9D2256 | Red   | MEDIUM |  3.00 |    2 |
| DPV0010260188 | DHRU124_1 | 00021331918437 | 9782189D |       |        |     4 |    1 |
+---------------+-----------+----------------+----------+-------+--------+-------+------+

1 个解决方案

#1

You could apply this to your current query, I would most likely use a variable table, to store the results from your query in, and then query the variable table like this...

您可以将此应用于当前查询,我很可能会使用变量表来存储查询中的结果,然后像这样查询变量表...

declare @example table (
    ExampleID int identity(1, 1) not null primary key clustered
,   Parcel  nvarchar(255) not null
,   [Order] nvarchar(255) not null
,   Item    nvarchar(255) not null
,   Alias   nvarchar(255) not null
,   Color   nvarchar(255) null
,   Size    nvarchar(255) null
,   Price   money not null
,   [Sent]  int not null
);

insert into @example (Parcel, [Order], Item, Alias, Color, Size, Price, [Sent])

select 'DPV0010260188' , 'DHRU124_1' , '00717851968853' , '9E9D2256' , 'Red'   , 'MEDIUM' ,  2.00 ,    1 union all
select 'DPV0010260188' , 'DHRU124_1' , '00717851968853' , '9E9D2256' , 'Red'   , 'MEDIUM' ,     1 ,    1 union all
select 'DPV0010260188' , 'DHRU124_1' , '00021331918437' , '9782189D' , null    , null     ,     4 ,    1;

select distinct Parcel
     , [Order]
     , Item
     , Alias
     , Color
     , Size
     , sum(Price) Price
     , sum([Sent]) [Sent]
  from @example
  group by Parcel, [Order], Item, Alias, Color, Size

Output:

Parcel          Order       Item            Alias       Color   Size    Price   Sent
DPV0010260188   DHRU124_1   00021331918437  9782189D    NULL    NULL    4.00    1
DPV0010260188   DHRU124_1   00717851968853  9E9D2256    Red     MEDIUM  3.00    2