SQL Server 2008. Take info from two tables and concatenate row values.

Question

I have looked at what was marked as the duplicate of this and it is not. I'm pulling from two tables, not one.

First, allow me to say I had nothing to do with the design of this database.

I have two tables that must be joined, and then an unknown amount of rows where the data must be concatenated into one giant string. They are joined by the Record ID.

Item table:

Item         RecordID
---------------------
Car A        123
Car B        456
Car C        789

Yes, the words literally cut off in the middle. There should be nothing added between the values, and I also need to keep the commas and other special characters.

Details table:

RecordID     Details
--------------------------------
123          black pain
123          t, radials
123          , green le
123          ather, spo
123          rt steerin
123          g wheel, b
123          uilt-in GP
123          S
456          standard
789          black leat
789          her, teles
789          coping ste
789          ering whee
789          l, seven c
789         up holders
789         , heavy du
789         ty mudflap
789         s

What I want to end up with is this:

ItemID       RecordID     Details
----------------------------------------------------------------------------
Car A        123          black paint, radials, green leather, sport steering wheel, built-in GPS
Car B        456          standard       
Car C        789          black leather, telescoping steering wheel, seven cup holders, heavy duty mudflaps

I've looked at all the XML ones and can't figure out how to do this.

Thanks in advance.

If you have no other columns, then you have a problem. SQL Data is inherently "un-ordered", there is no way to know which order to concatenate the records without another column to order it by. — MatBailie, Oct 09 '18 at 21:47
for completeness in case someone finds their way here: in SQL Server 2017 there is STRING_AGG — Mitch Wheat, Oct 10 '18 at 12:23

score 0 · Accepted Answer · answered Oct 09 '18 at 22:20

There is no guarantee that your STUFF/ FOR XML PATH will produce the results you ask for unless you have an IDENTITY field in your Details table or some other value that you can sort by that will force the order of the Details text.

Usually you could use the STUFF command with an ORDER BY statement

SELECT
  Item.Item AS ItemID,
  Item.RecordID,
  STUFF( (
           SELECT
             '' + Details
           FROM
             Details
           WHERE
             Details.RecordID = Item.RecordID
             -- ORDER BY SomeLineIndicator
           FOR XML PATH ('')
         ), 1, 0, '' ) AS Details
FROM
  Item

I tried this on my box without the ORDER BY and just so happened to get the result you're asking for, but you really can't rely on these results without a field you can use to force the order.

Please read this post and the linked articles for more information about why you'd need a field for this and why you can't depend on an undetermined internal "index" to take care of it for you: Default row order in SELECT query - SQL Server 2008 vs SQL 2012

There is a field I can order them on. Thank you, thank you, thank you. I curse the person who created the second table! — BethD, Oct 10 '18 at 12:43

SQL Server 2008. Take info from two tables and concatenate row values.

1 Answers1