4

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'

I need the output like this:

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

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

Dale K
  • 25,246
  • 15
  • 42
  • 71
DeePak
  • 135
  • 1
  • 9
  • share your attempts code – Zaynul Abadin Tuhin Jul 10 '19 at 06:06
  • ```select T0.Item,T0.Wh,STUFF((SELECT DISTINCT '; ' + T0.Batch FROM OIBT US WHERE US.DOCNUM = '3' AND US.Wh = 'FN' FOR XML PATH('')), 1, 1, '') [BATCH REF NO] from OIBT T0 WHERE T0.DOCNO = '3' AND T0.WH = 'FN' GROUP BY T0.ITEM,T0.WH,t0.Batch ORDER BY T0.Item``` – DeePak Jul 10 '19 at 06:09
  • @APDP include your code with the question – MJoy Jul 10 '19 at 06:18

3 Answers3

4
SELECT ITEM, 
       WH, 
       BATCH = STUFF((SELECT ',' + BATCH
                      FROM oibt
                      WHERE [DOC NO] = '3'
                      GROUP BY BATCH
                      FOR XML PATH ('')), 1, 1, '') 
FROM oibt
GROUP BY ITEM, WH
Arulkumar
  • 12,966
  • 14
  • 47
  • 68
B.Muthamizhselvi
  • 642
  • 4
  • 13
  • This solution is amazing and powerful. I worked for a few days trying to solve this logical problem giving up multiple times, where you have a two sets and you need to choose one of the grouped sets by precedence. Thank you so much, so much more elegant! – jwize Jul 10 '19 at 06:38
2

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
MJoy
  • 1,349
  • 2
  • 9
  • 23
1

If you are using SQL Server 2016 or higher version, you can make use of STRING_AGG built in function without having to do the tedious traditional combination of XML FOR and STUFF:

SELECT  ITEM
        ,WH
        ,BATCH = STRING_AGG(BATCH,',')
FROM
(
    SELECT  DISTINCT 
            ITEM, WH, BATCH
    FROM    OIBT O
    WHERE   DOCNO = '3' 
    AND     Wh = 'FN'
) IQ
GROUP   BY ITEM, WH
san
  • 1,415
  • 8
  • 13