SQL server, how to concat a dymanic (unknown) amout of strings with a single query

Question

I know how to concact two strings, but this one will be a bit more complicated, i guess it's a duplicate but i could not find it after an hour of search so....

Suppose i have a table like this :

ExempleTable
____________________
  ID   |  TEXT      |
___________________ |
 1     |  'hello '  |
 2     |  'how are '|
 3     |  'you ?'   |

I need a select that would return a single line containing :

'hello how are you ?'

I thought something like this would exist :

CONCATTEXT( SELECT text FROM ExampleTable) as sentence

But no... maybe it's a GROUP BY matter ? I can't figure it out...

No... guys, i'm asking if it's possible to do it in a single query, not by all means with temporary tables and everything, if it's not possible in a single query well... too bad — Antoine Pelletier, Jan 13 '17 at 17:08
See the one marked by @Siyual. The reason those temp tables are used is to create a table for the query. Just use your actual table there instead of the temp table. — Sean Lange, Jan 13 '17 at 17:09
None of those methods used temporary tables outside of generating test data. — Siyual, Jan 13 '17 at 17:09
ok sorry just by looking at it, it seemed more complicated, i will take a second look — Antoine Pelletier, Jan 13 '17 at 17:10
@Siyual in fact it's pretty much like my question, i accepted mine as duplicate — Antoine Pelletier, Jan 13 '17 at 17:30
WITH rquery (testid, sentence) AS ( SELECT base.testid, base.text FROM testtable base WHERE testid = 1 UNION ALL SELECT t1.testid, trim(sentence) || ' ' || trim(t1.text) FROM rquery t0, testtable t1 WHERE t0.testid + 1 = t1.testid )SELECT * FROM rquery where testid= (select max(testid) from rquery ) — , Jan 13 '17 at 18:31
@AntoinePelletier: No checkout the query. Its small and concatenates column values into a single row — , Jan 13 '17 at 18:34
Yeah might be ok, nice work, Jhon Cappelletti's query is a bit more simple though ;) — Antoine Pelletier, Jan 13 '17 at 18:44

John Cappelletti · Accepted Answer · 2017-01-13T17:24:40.737

1

Declare @YourTable table (ID int,TEXT varchar(50))
 Insert Into @YourTable values
 (1,'hello '),
 (2,'how are '),
 (3,'you ?')

Select Stuff((Select ' '+ltrim(rtrim(Text)) From @YourTable Order by ID For XML Path ('')),1,1,'')

Returns

hello how are you ?

edited Jan 13 '17 at 17:24

answered Jan 13 '17 at 17:11

John Cappelletti

79,615
7
44
66

can't use this, SINGLE QUERY – Antoine Pelletier Jan 13 '17 at 17:14
See Edit Select Stuff(( – John Cappelletti Jan 13 '17 at 17:16
Thank you, this exemple is very simple, sorry my question was a duplicate – Antoine Pelletier Jan 13 '17 at 17:24
@AntoinePelletier see update. I added ltrim(rtrim(text)) to avoid double spaces – John Cappelletti Jan 13 '17 at 17:25
@AntoinePelletier Happy it helped – John Cappelletti Jan 13 '17 at 17:25
strange though it made some tags appear : `` and ` ` like it was html or something, not that it matters much i can remove them easely – Antoine Pelletier Jan 13 '17 at 17:28
1

@AntoinePelletier You must have some extended characters/tags in your data. Data-Hygiene is a constant battle :) – John Cappelletti Jan 13 '17 at 17:31
Yeah i see... Data-Hygiene, funny word, strong meaning – Antoine Pelletier Jan 13 '17 at 17:33

score 0 · Answer 2 · answered Jan 13 '17 at 17:11

0

You could use a variable:

declare @result varchar(max) = ''

select @result += field + ' ' from T

select rtrim(@result)

answered Jan 13 '17 at 17:11

Alex K.

171,639
30
264
288

No i can't use variable – Antoine Pelletier Jan 13 '17 at 17:13

SQL server, how to concat a dymanic (unknown) amout of strings with a single query

2 Answers2