如何通过分组将它们合并成多个列?

If you run the below code you will get a result that looks like the following:

如果您运行以下代码，您将得到如下结果:

ITEM    DIFF_QTY        DATE_OUTGO                              WAREH  COMPANY
1000    -5, -3, -4,     2017-08-01, 2017-08-02, 2017-08-03,     WH     CMP

Which is great, this is the output that I am looking for. Sadly this only works when a specific ITEM is selected. If I remove the WHERE then everything is put on the same row of output.

很好，这是我要找的输出。遗憾的是，这只在选择特定项目时才有效。如果我移走了，那么所有东西都放在同一排输出上。

So how would I separate the results by grouping (ITEM, WAREH, COMPANY)? So that the end result would look like this:

那么，如何将结果分组(项目、仓库、公司)?最终结果是这样的

ITEM    DIFF_QTY        DATE_OUTGO                              WAREH  COMPANY
1000    -5, -3, -4,     2017-08-01, 2017-08-02, 2017-08-03,     WH     CMP
1001    -10, -13        2017-08-01, 2017-08-03                  WH     CMP

Code

DECLARE @tempTable TABLE (
    ITEM nvarchar(32),
    DIFF_QTY nvarchar(6), 
    DATE_OUTGO nvarchar(10),
    WAREH nvarchar(5),
    COMPANY nvarchar(5)
)

INSERT INTO @tempTable (ITEM, DIFF_QTY, DATE_OUTGO, WAREH, COMPANY)
VALUES 
(1000, '-5', '2017-08-01', 'WH', 'CMP'),
(1000, '-3', '2017-08-02', 'WH', 'CMP'),
(1000, '-4', '2017-08-03', 'WH', 'CMP'),
(1001, '-10', '2017-08-01', 'WH', 'CMP'),
(1001, '-13', '2017-08-03', 'WH', 'CMP')

SELECT * 
FROM @tempTable

DECLARE @itemNum nvarchar(32)
DECLARE @diffQty nvarchar(max)
DECLARE @dateOutgo nvarchar(max)
DECLARE @warehouse nvarchar(5)
DECLARE @company nvarchar(5)

SET @diffQty = ''
SET @dateOutgo = ''

SELECT 
    @itemNum = ITEM, 
    @diffQty = @diffQTY + DIFF_QTY + ', ',
    @dateOutgo = @dateOutgo + DATE_OUTGO + ', ', 
    @warehouse = WAREH, 
    @company = COMPANY 
FROM 
    @tempTable
WHERE 
    ITEM = 1000

SELECT @itemNum ITEM_NUM, @diffQty DIFF_QTY, @dateOutgo DATE_OUTGO, @warehouse WAREHOUSE, @company COMPANY

I've looked at multiple threads about coalesce but pretty much every solution requires that you don't do multiple things at the same time (SELECT, GROUP, COALESCE, Multiple columns etc...)

我研究过关于合并的多个线程，但是几乎每个解决方案都要求您不要同时做多个事情(选择、组、合并、多列等等)。

3 个解决方案

#1

You cannot use scalar variables, like @itemNum nvarchar(32), to store tabular data.

不能使用标量变量(如@itemNum nvarchar(32))来存储表格数据。

The output you want to achieve is most commonly generated in SQL Server using FOR XML PATH:

您希望实现的输出通常是在SQL Server中为XML路径生成的:

SELECT t.ITEM,
       STUFF((SELECT  ',' + x.DIFF_QTY
              FROM @tempTable AS x
              WHERE x.ITEM = t.ITEM AND 
                    x.WAREH = t.WAREH AND
                    x.COMPANY = t.COMPANY
              FOR XML PATH('')), 1, 1, '') AS DIFF_QTY,
       STUFF((SELECT  ',' + x.DATE_OUTGO
              FROM @tempTable AS x
              WHERE x.ITEM = t.ITEM AND 
                    x.WAREH = t.WAREH AND
                    x.COMPANY = t.COMPANY
              FOR XML PATH('')), 1, 1, '') AS DATE_OUTGO
FROM @tempTable AS t
GROUP BY t.ITEM, t.WAREH, t.COMPANY

Output:

输出:

ITEM    DIFF_QTY    DATE_OUTGO
-----------------------------------------------------
1000    -5,-3,-4    2017-08-01,2017-08-02,2017-08-03
1001    -10,-13     2017-08-01,2017-08-03

#2

You can use FOR XML PATH('') to concatenate the values:

可以使用XML PATH(")将值串联起来:

SELECT
    t1.ITEM,
    a.DIFF_QTY,
    b.DATE_OUTGO,
    t1.WAREH,
    t1.COMPANY
FROM @tempTable t1
CROSS APPLY(
    SELECT DIFF_QTY =  STUFF((
        SELECT ', ' + CAST(t2.DIFF_QTY AS VARCHAR(100))
        FROM @tempTable t2
        WHERE
            t2.ITEM = t1.ITEM
            AND t2.WAREH = t1.WAREH
            AND t2.COMPANY = t2.COMPANY
        ORDER BY t2.DATE_OUTGO
        FOR XML PATH('')
    ), 1, 1, '')  
) a
CROSS APPLY(
    SELECT DATE_OUTGO = STUFF((
        SELECT ', ' + CAST(t2.DATE_OUTGO AS VARCHAR(10))
        FROM @tempTable t2
        WHERE
            t2.ITEM = t1.ITEM
            AND t2.WAREH = t1.WAREH
            AND t2.COMPANY = t2.COMPANY
        ORDER BY t2.DATE_OUTGO
        FOR XML PATH('')
    ), 1, 1, '')  
) b
GROUP BY
    t1.ITEM, t1.WAREH, t1.COMPANY, a.DIFF_QTY, b.DATE_OUTGO

#3

DECLARE @tempTable TABLE (
    ITEM nvarchar(32),
    DIFF_QTY nvarchar(6), 
    DATE_OUTGO nvarchar(10),
    WAREH nvarchar(5),
    COMPANY nvarchar(5)
)

INSERT INTO @tempTable (ITEM, DIFF_QTY, DATE_OUTGO, WAREH, COMPANY)
VALUES 
(1000, '-5', '2017-08-01', 'WH', 'CMP'),
(1000, '-3', '2017-08-02', 'WH', 'CMP'),
(1000, '-4', '2017-08-03', 'WH', 'CMP'),
(1001, '-10', '2017-08-01', 'WH', 'CMP'),
(1001, '-13', '2017-08-03', 'WH', 'CMP')

SELECT * 
FROM @tempTable

DECLARE @itemNum nvarchar(32)
DECLARE @diffQty nvarchar(max)
DECLARE @dateOutgo nvarchar(max)
DECLARE @warehouse nvarchar(5)
DECLARE @company nvarchar(5)

SET @diffQty = ''
SET @dateOutgo = ''



SELECT distinct  ITEM , 

              STUFF((SELECT ', ' + CAST(DIFF_QTY AS NVARCHAR(MAX)) [text()]
         FROM  @tempTable temp
         where t.ITEM=temp.ITEM                                         
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,'')  DIFF_QTY, 

                      STUFF((SELECT ', ' + CAST(DATE_OUTGO AS NVARCHAR(MAX)) [text()]
         FROM  @tempTable temp      
          where t.ITEM=temp.ITEM                                   
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,'')  DATE_OUTGO,
        WAREH,COMPANY
        from @tempTable t

Hope it works exactly you want

希望它能如你所愿

#1