[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

T-SQL Combining Data in One Field

Posted on 2013-06-13
5
Medium Priority
?
305 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 49

Accepted Solution

by:
PortletPaul earned 2000 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

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Screencast - Getting to Know the Pipeline

829 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