Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
788 views
in Technique[技术] by (71.8m points)

sql server - 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'

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)
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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...