CODEWITHSUNDEEP
sqlsql-serverstored-procedures
YHerrera
YHerrera

Reputation: 45

Stored Procedure that Concatenates Numbers

I have a stored procedure that concatenates tracking numbers from multiple records into one.

This stored procedure below works fine

ALTER PROCEDURE [dbo].[Proc1]
   @HWOrderID NVARCHAR(20)
AS
   DECLARE @concatn NVARCHAR(MAX)

   SELECT 
      @concatn = COALESCE(@concatn + ' ','') +
                 COALESCE(IT.TrackingNum + ', ','')  
   FROM 
      HWEvent as hwe
   INNER JOIN 
      HWOrderID AS hwo ON hwo.HWEventpk = hwe.HWEventpk
   LEFT OUTER JOIN  
      InventoryTransaction IT ON hwo.HWEventpk = IT.HWEventpk
   WHERE
       hwo.HWOrderID = 21000
       AND IT.FromPartypk = '13EF4AF5-0957-465C-BE20-C057EC970B5B'

   SELECT 
      @concatn AS 'FromSMHS'

   SET @concatn = null

My results:

FromSMHS
5560,  5561,  5562,  5563,  5564,  5565

But... now I need to add additional tracking numbers from a second table. When I join the additional tables I get duplicate tracking numbers.

ALTER PROCEDURE [dbo].[Proc2]
    @HWOrderID NVARCHAR(20)
AS
   DECLARE @concatn NVARCHAR(MAX)

   SELECT 
      @concatn = COALESCE(@concatn + ' ','') +
                 COALESCE(IT.TrackingNum + ', ','') +
                 COALESCE(fexd.TrackingNumber + ', ','')  
   FROM
      HWEvent as hwe
   INNER JOIN 
      HWOrderID AS hwo ON hwo.HWEventpk = hwe.HWEventpk
   LEFT OUTER JOIN  
      InventoryTransaction IT ON hwo.HWEventpk = IT.HWEventpk
   LEFT OUTER JOIN   
      FedexTrackingReference fexr ON hwe.HWEventID = fexr.HWEventID
   LEFT OUTER JOIN  
      FedExTrackingDetail fexd ON fexr.FedexTrackingDetailpk =  fexd.FedExTrackingDetailpk
   WHERE
      hwo.HWOrderID = 21000
      AND IT.FromPartypk = '13EF4AF5-0957-465C-BE20-C057EC970B5B'

   SELECT @concatn AS 'FromSMHS'
   SET @concatn = null

My results:

FromSMHS
5560, 5561  5560, 8500,5560, 8501,  5560, 8502,  5562, 5561  5562, 8500,5562, 8501,  5562, 8502,  5563, 5561  5563, 8500,5563, 8501,  5563, 8502,  5564, 5561  5564, 8503, 8501,  5564, 8502,  5564, 5561  5564, 8503, 8501,  5564, 8502,  5565, 5561  5565, 8500,5565, 8501,  5565, 8502

My desired result should like this:

FromSMHS
5560, 5561, 5562, 5563, 5564,  5565, 8500, 8501, 8502,8503

Upvotes: 4

Views: 74

Answers (2)

M.Ali
M.Ali

Reputation: 69594

Try this....

ALTER PROCEDURE [dbo].[Proc2]
@HWOrderID NVARCHAR(20)
AS
BEGIN
    SET NOCOUNT ON;

SELECT DISTINCT
       STUFF((SELECT ', ' +  CAST(TrackingNum AS VARCHAR(100))
              FROM InventoryTransaction
              WHERE HWEventpk = IT.HWEventpk
              FOR XML PATH(''),TYPE)
              .value('.','NVARCHAR(MAX)'),1,2,'')

 + ' ' +STUFF((SELECT ', ' +  CAST(TrackingNumber AS VARCHAR(100))
              FROM FedExTrackingDetail
              WHERE fexr.FedexTrackingDetailpk = FedexTrackingDetailpk
              FOR XML PATH(''),TYPE)
              .value('.','NVARCHAR(MAX)'),1,2,'')  AS Result_List             

FROM  HWEvent as hwe
INNER JOIN  HWOrderID AS hwo            ON hwo.HWEventpk = hwe.HWEventpk  AND hwo.HWOrderID = @HWOrderID
LEFT  JOIN  InventoryTransaction IT     ON hwo.HWEventpk = IT.HWEventpk
                                       AND IT.FromPartypk = '13EF4AF5-0957-465C-BE20-C057EC970B5B'
LEFT  JOIN  FedexTrackingReference fexr ON hwe.HWEventID = fexr.HWEventID
LEFT  JOIN  FedExTrackingDetail fexd    ON fexr.FedexTrackingDetailpk =  fexd.FedExTrackingDetailpk

END

Upvotes: 1

db_brad
db_brad

Reputation: 923

I would union the results from the 2 tables and then I personally like using the FOR XML PATH trick to get a comma delimited string from multiple rows.

How to collate rows to a delimited string in SQL2008R2

Upvotes: 1

Related Questions