red white puzzle

Concatenating strings in SQL Server


This SQL Server tip comes from the April 2012 edition of the GP Reports Viewer newsletter.

A request that we have seen many times when creating SOP invoices for Dynamics GP is to concatenate serial numbers.  So if there is an item with serial numbers, instead of showing a list with one serial number per line under the item, which would look a little unwieldy with long list of serial numbers:

we want to show all the serial numbers on one line, separated by a comma. Below is the SQL code that will do this in SQL 2005 and 2008:

SELECT
p.SOPNUMBE SOP_Number, p.SOPTYPE SOP_Type, p.ITEMNMBR Item, 
p.LNITMSEQ Line_Item_Sequence,
LEFT(p.serial_numbers, len(p.serial_numbers)-1) Serial_Numbers
FROM
 (SELECT p1.SOPNUMBE, p1.SOPTYPE, p1.ITEMNMBR, p1.LNITMSEQ,
  (SELECT coalesce(rtrim(SERLTNUM) + ', ','')
   FROM SOP10201 p2
   WHERE p1.SOPNUMBE = p2.SOPNUMBE 
      and p1.SOPTYPE = p2.SOPTYPE
      and p1.ITEMNMBR = p2.ITEMNMBR 
      and p1.LNITMSEQ = p2.LNITMSEQ
   ORDER BY SOPNUMBE
   FOR XML PATH('')) serial_numbers
  FROM SOP10201 p1
 GROUP BY SOPNUMBE, SOPTYPE, ITEMNMBR, LNITMSEQ) p

Using this code, the line item and serial numbers shown above can look like this:

This code can also be changed slightly for other uses, for example, to concatenate a list of invoices paid by a check in payables. Here is the code to do that using my Payables Apply Detail view:

SELECT
p.Vendor_ID, p.Payment_Voucher_Number, 
p.Payment_Date, p.Payment_Type,
p.Payment_Document_Number, 
LEFT(p.apply_docs, len(p.apply_docs)-1) Applied_To_Docs
FROM
 (SELECT p1.Vendor_ID, p1.Payment_Voucher_Number, 
  p1.Payment_Date, p1.Payment_Type,
  p1.Payment_Document_Number,
  (SELECT coalesce(rtrim(Apply_To_Doc_Number) + ', ','')
   FROM view_Payables_Apply_detail p2
   WHERE p1.Vendor_ID = p2.Vendor_ID
     and p1.Payment_Voucher_Number = p2.Payment_Voucher_Number
   ORDER BY Payment_Voucher_Number
   FOR XML PATH('')) apply_docs
  FROM view_Payables_Apply_detail p1
  GROUP BY Vendor_ID, Payment_Voucher_Number, Payment_Date, 
    Payment_Type, Payment_Document_Number) p

For more tips like this, take a look at my SQL Server Coding Tips page. To receive new tips as soon as they are published, sign up for the GP Reports Viewer newsletter.

Trackbacks/Pingbacks

  1. Dynamics GP SOP line items with serial numbers and comments – 6/4, Victoria Yudin | Partner Compete - June 4, 2012

    […] requests, this new view is a combination of  my All SOP Line Items view and the recently posted script showing how to get a concatenated list of serial numbers in SQL. This new view will return all posted (history) or unposted (open) Sales Order Processing line […]

    Like

  2. Dynamics GP SOP line items with serial numbers and comments - Victoria Yudin - GP Technical Blogs - Microsoft Dynamics Community - June 4, 2012

    […] requests, this new view is a combination of  my All SOP Line Items view and the recently posted script showing how to get a concatenated list of serial numbers in SQL. This new view will return all posted (history) or unposted (open) Sales Order Processing line […]

    Like

  3. Dynamics GP SOP line items with serial numbers and comments | Victoria Yudin - June 4, 2012

    […] requests, this new view is a combination of  my All SOP Line Items view and the recently posted script showing how to get a concatenated list of serial numbers in SQL. This new view will return all posted (history) or unposted (open) Sales Order Processing line […]

    Like

  4. Concatenating strings in SQL Server | Victoria Yudin - DynamicAccounting.net - GP Technical Blogs - Microsoft Dynamics Community - May 22, 2012

    […] Comments 0 Victoria Yudin shows off Concatenating strings in SQL Server […]

    Like

  5. Concatenating strings in SQL Server | Interesting Findings & Knowledge Sharing - May 21, 2012

    […] more here: Concatenating strings in SQL Server VN:F [1.9.17_1161]Please wait while your rating is cast…Rating: 0.0/10 (0 votes cast)VN:F […]

    Like

  6. Concatenating strings in SQL Server – 5/21, Victoria Yudin | Partner Compete - May 21, 2012

    […] Continue reading on Source Blog […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,640 other followers

%d bloggers like this: