查询以将多行合并为单行 [英] Query to get multiple row into single row

问题描述

我有一张桌子，上面有以下信息:

I have a table in which following information are there:

ITEM  WH  BATCH  DOC NO
CLD1  FN   B1      3
CLD1  FN   B1      3
CLD1  FN   B2      3
CLD1  FN   B2      3
CLD1  FN   B3      3
CLD1  FN   B4      3

这是我用来带来上述值的代码:

This is the code which I have used to bring the above values:

select T0.item,t0.wh,t0.batchnum from oibt t0 where t0.DOCNO = '3' and t0.Wh = 'FN'

我需要这样的输出:

ITEM  WH  BATCH
CLD1  FN   B1,B2,B3,B4

我用过STUFF&对于XML编码也是如此，但是我没有得到想要的输出.

I have used STUFF & For XML coding too but I am not getting the desired output.

推荐答案

以下查询应执行您想要的操作:

The following query should do what you want:

SELECT ITEM
    ,WH
    ,BATCH = STUFF( (SELECT DISTINCT ', ' + BATCH FROM table1 t WHERE t.ITEM = ITEM FOR XML PATH ('')),1,1,'')
FROM table1
GROUP BY ITEM, WH

根据您的代码示例进行了更新，

Updated as per your code sample,

SELECT T0.Item
    ,T0.Wh
    ,[BATCH REF NO]  = STUFF((SELECT DISTINCT '; ' + US.Batch FROM OIBT US WHERE US.DOCNO = '3' AND US.Wh = Wh AND US.ITEM = ITEM FOR XML PATH('')), 1, 1, '')
FROM OIBT T0 
WHERE T0.DOCNO = '3' AND T0.WH = 'FN' 
GROUP BY T0.ITEM,T0.WH ORDER BY T0.Item 

这篇关于查询以将多行合并为单行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！