Solved

T-SQL Combining Data in One Field

Posted on 2013-06-13
5
297 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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql how to count case when 4 29
domain login has permission in database, but gets error 3 26
Job - date manual 1 43
shrink table after huge delete 2 28
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

733 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