Solved

T-SQL Combining Data in One Field

Posted on 2013-06-13
5
299 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 49

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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

717 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