• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

T-SQL Combining Data in One Field

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
TBSupport
Asked:
TBSupport
  • 3
1 Solution
 
Christopher GordonSenior Developer AnalystCommented:
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
 
PortletPaulfreelancerCommented:
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
 
TBSupportAuthor Commented:
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
 
TBSupportAuthor Commented:
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
 
TBSupportAuthor Commented:
Disregard my last question.  I fixed it by using      select replace('abcde   ', ' ', '')

TBSupport
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now