Solved

T-SQL Combining Data in One Field

Posted on 2013-06-13
5
295 Views
Last Modified: 2013-06-13
Hello:

Let's say that you have a table with two columns.  One column is order number, while the other column is tracking number.

Further, let's say that you can have more than one tracking number for each order.

Now, say that I want to do a select statement that displays one record for every order number.  Even if there is more than one tracking number, I want each tracking number in that one record.  

So, let's say that for one order there are the following tracking numbers for one order:

1X200
2x300
3X400

And, let's say that the order number is 1234.

I want the following records returned:

1234     1X200, 2X300, 3X400

What syntax do I use, to do so?

Thanks!

TBSupport
0
Comment
Question by:TBSupport
  • 3
5 Comments
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 39244621
Lots of ways to do this.  See attached example

select distinct
      Id
,      rtrim(substring(isnull((select ','+ value from  @myTable t2 where t1.Id = t2.Id for xml path('')),' '),2,2000)) ValueConcat
     
from  @myTable t1
concat-example.sql
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39244636
and here's a good article discussing many of those approaches (such as that provided above, plus others)

https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/
0
 
LVL 1

Author Comment

by:TBSupport
ID: 39245083
Thanks!  After reviewing the article pointed out by PortletPaul, I decided to go with the following:

SELECT p1.SOPNUMBE,
       (SELECT rtrim(Tracking_Number) + ','
           FROM SOP10107 p2
          WHERE p2.SOPNUMBE = p1.SOPNUMBE
          ORDER BY Tracking_Number
            FOR XML PATH('')) AS TrackingNo
      FROM SOP10107 p1
      GROUP BY SOPNUMBE

The only problem with this is that it leaves a comma at the end of the data.  I have no clue what the length of the data is going to be.  So, I can't use any of the standard T-SQL string code to remove the comma based on length of data.  Any ideas on how to remove that last comma?

Thanks!  This has been very much appreciated!

TBSupport
0
 
LVL 1

Author Comment

by:TBSupport
ID: 39245210
Hi:

I got rid of that last comma by running the script below instead.  But, now, I get trailing spaces after each tracking number.  I've tried using rtrim.  That did not work, though.

Any thoughts on how I can get rid of the blank spaces?

SELECT p1.SOPNUMBE,
       stuff( (SELECT ','+Tracking_Number
               FROM SOP10107 p2
               WHERE p2.SOPNUMBE = p1.SOPNUMBE
               ORDER BY Tracking_Number
               FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
            ,1,1,'')
       AS TrackingNo
      FROM SOP10107 p1
      GROUP BY SOPNUMBE

TBSupport
0
 
LVL 1

Author Comment

by:TBSupport
ID: 39245683
Disregard my last question.  I fixed it by using      select replace('abcde   ', ' ', '')

TBSupport
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question