行字段值拼接成字符串

效果：

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