行字段值拼接成字符串

效果：

SizeBatchNo	SizingDyeBatchs
SZ1281656	B2081036/B2081037/B2081042

 

 1  WITH    SizingJob ( SizeBatchNo, BatchNo )
 2            AS (  SELECT   b.SizeBatchNo ,
 3                         b.BatchNo
 4                 FROM     WVMDB.dbo.wvSizingBatchInfo a  WITH ( NOLOCK )
 5                          LEFT  JOIN ydmdb.dbo.ydbeambatchno_sizing b  WITH ( NOLOCK )  ON a.Sizing_Batch_NO  = b.SizeBatchNo
 6                 WHERE    a.End_Time  IS  NULL
 7              )
 8      SELECT  SizeBatchNo ,
 9              CASE  WHEN  LEN(SizingDyeBatchs)  >  0
10                   THEN  STUFF(SizingDyeBatchs,  LEN(SizingDyeBatchs),  1,  '')
11                   ELSE  ''
12              END  AS SizingDyeBatchs
13      FROM    (  SELECT  DISTINCT
14                         SizeBatchNo
15                FROM      SizingJob
16             ) a
17              CROSS APPLY (  SELECT    SizingDyeBatchs  = (  SELECT
18                                                               BatchNo  +  ' / '
19                                                          FROM  SizingJob
20                                                          WHERE SizeBatchNo  = A.SizeBatchNo
21                            FOR       XML PATH( '') ,
22                                         TYPE
23             ).value( ' / ',  ' nvarchar(max) ')
24

 1  WITH    SizingJob ( SizeBatchNo, BatchNo )
 2            AS (  SELECT   b.SizeBatchNo ,
 3                         b.BatchNo
 4                 FROM     WVMDB.dbo.wvSizingBatchInfo a  WITH ( NOLOCK )
 5                          LEFT  JOIN ydmdb.dbo.ydbeambatchno_sizing b  WITH ( NOLOCK )  ON a.Sizing_Batch_NO  = b.SizeBatchNo
 6                 WHERE    a.End_Time  IS  NULL
 7              )
 8      SELECT  SizeBatchNo ,
 9              CASE  WHEN  LEN(SizingDyeBatchs)  >  0
10                   THEN  STUFF(SizingDyeBatchs,  LEN(SizingDyeBatchs),  1,  '')
11                   ELSE  ''
12              END  AS SizingDyeBatchs
13      FROM    (  SELECT  DISTINCT
14                         SizeBatchNo
15                FROM      SizingJob
16             ) a
17              CROSS APPLY (  SELECT    SizingDyeBatchs  = (  SELECT
18                                                               BatchNo  +  ' / '
19                                                          FROM  SizingJob
20                                                          WHERE SizeBatchNo  = A.SizeBatchNo
21                            FOR       XML PATH( '') ,
22                                         TYPE
23             ).value( ' / ',  ' nvarchar(max) ')
24                         ) b

转载于:https://www.cnblogs.com/yooplmqj/archive/2012/09/21/2696869.html