1

I have two tables. One has an Order number, and details about the order:

CREATE TABLE #Order ( OrderID int )

and the second contains comments about the order:

CREATE TABLE #OrderComments ( OrderID int
                              Comment VarChar(500) )
Order ID      Comments
~~~~~~~~      ~~~~~~~~
1             Loved this item!
1             Could use some work
1             I've had better
2             Try the veal

I'm tasked with determining the maximum length of the output, then returning output like the following:

Order ID      Comments                                                  Length
~~~~~~~~      ~~~~~~~~                                                  ~~~~~~
1             Loved this item! | Could use some work | I've had better  56
2             Try the veal                                              12

So, in this example, if this is all of the data, I'm looking for "56").

The main purpose is to determine the maximum length of all comments when appended together, including the | delimiter. This will be used when constructing the table this output will be put into, to determine if we can get the data within the 8,060 size limit for a row or if we need to use varchar(max) or text to hold the data.

I have tried a couple of subqueries that can generate this output to variables, but I haven't found one yet that could generate the above output. If I could get that, then I could just do a SELECT TOP 1 ... ORDER BY 3 DESC to get the number I'm looking for.

Daniel Bragg
  • 1,773
  • 1
  • 12
  • 25

2 Answers2

1

To find out what the length of the longest string will be if you trim and concatenate all the (not null) comments belonging to an OrderId with a delimiter of length three you can use

SELECT TOP(1) SUM(LEN(Comment)) + 3* (COUNT(Comment) - 1) AS Length
FROM OrderComments
GROUP BY OrderId
ORDER BY Length DESC

To actually do the concatenation you can use XML PATH as demonstrated in many other answers on this site.

WITH O AS
(
SELECT DISTINCT OrderID
FROM #Order
)
SELECT O.OrderID,
       LEFT(y.Comments, LEN(y.Comments) - 1) AS Comments
FROM   O
       CROSS APPLY (SELECT ltrim(rtrim(Comment)) + ' | '
                    FROM   #OrderComments oc
                    WHERE  oc.OrderID = O.OrderID
                    AND Comment  IS NOT NULL
                    FOR XML PATH(''), TYPE) x (Comments)
       CROSS APPLY (SELECT x.Comments.value('.', 'VARCHAR(MAX)')) y(Comments)
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • The crazy thing is that I hit upon this answer within minutes of posting the question. Now, any idea how to generate the output above, with the values concatenated together? – Daniel Bragg Dec 09 '14 at 21:57
  • I had a look at the `XML PATH`, but also read that it shouldn't be used if `&` and `<` could be present in the text, which this could easily contain those. – Daniel Bragg Dec 09 '14 at 21:58
  • @Dan it can be used but you need to use it a bit differently than the more common code found. See http://stackoverflow.com/a/1052353/73226 – Martin Smith Dec 09 '14 at 22:00
  • Is there a variation of `CONCAT` that is usable on versions of SQL Server older than 2014? – Daniel Bragg Dec 09 '14 at 22:16
  • `CONCAT` doesn't concatenate across rows anyway. I've edited the [answer here](http://stackoverflow.com/a/451441/73226) to include a version that works with those characters and added a version to this answer. – Martin Smith Dec 09 '14 at 22:25
  • I looked at the link you provided, and brought it into a new query window so I could start dissecting it, but it complains with `Invalid object name 'Numbers'`. The OP of that thread didn't mention what ver of SQL he was working with, but I'm guessing it's newer than 2008? – Daniel Bragg Dec 09 '14 at 22:27
  • @Dan - You would need to change the table names to select from your tables. – Martin Smith Dec 09 '14 at 22:29
  • Change that to a `SELECT DISTINCT O.OrderID` and it's a perfect fit! Thanks! – Daniel Bragg Dec 09 '14 at 22:29
  • @Dan - Ah OK, assumed that would be the PK of Orders. – Martin Smith Dec 09 '14 at 22:30
  • 1
    Doh! of course. Here I thought it was some new feature of a newer SQL Server... (facepalm) – Daniel Bragg Dec 09 '14 at 22:30
  • @Dan - Lots of people have such a table as standard for reasons here [Why are numbers tables “invaluable”?](http://dba.stackexchange.com/q/11506/3690) – Martin Smith Dec 09 '14 at 22:33
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/66523/discussion-between-dan-and-martin-smith). – Daniel Bragg Dec 09 '14 at 22:56
0

All you need is STUFF function and XML PATH

Check out this sql fiddle http://www.sqlfiddle.com/#!3/65cc6/5

NiralZaveri
  • 21
  • 2