T-SQL, Select @variable within a sub-query causes syntax error

Question

I'm using a sub-query to get results needed (multiple records returned), and I want to put those results in a single record returned.

When I run the sub-query on its own, it works, but once I use it as a sub query, it no longer works due to a syntax error.

The following code causes a syntax error

(Incorrect syntax near '='.)

declare @test varchar(1000)
set @test = ''  

SELECT description, (SELECT @test = @test + FirstName
                FROM EMP_tblEmployee
                )select @test
FROM EMP_tblCrew

So essentially, the sub query

(SELECT @test = @test + FirstName
                    FROM EMP_tblEmployee
                    )select @test

returns "charliejohnjacob"

The main query

SELECT description FROM EMP_tblCrew

returns "janitor"

So I want it to say

janitor | charliejohnjacob

2 fields, 1 record.

Are you trying to transform rows into columns, or add a space to firstName's — StingyJack, Dec 10 '10 at 16:40
Looks like an attempt to concatenate all the first names into a result set... but why? This, if it works, will create first name pyramid of sorts. The intent here is highly questionable. — Paul Sasik, Dec 10 '10 at 16:43

Paul Sasik · Accepted Answer · 2010-12-10T16:48:58.010

1

Your query is not syntactically correct and the T-SQL parser has a nasty habit of not reporting an error quite accurately at times. This is a bit of a stab in the dark but try:

SELECT 
description, 
(SELECT FirstName + ' ' FROM EMP_tblEmployee FOR XML PATH('')) AS [Name Concat Result]
FROM EMP_tblCrew

That will fix one thing at least, though I'm not sure how SQL server feels about concatenating inline like that. You also risk overflowing the varchar(1000) if your table is of appreciable size. Even varchar 8000 isn't very much for this kind of query.

edited Dec 10 '10 at 16:48

answered Dec 10 '10 at 16:41

Paul Sasik

79,492
20
149
189

Hi, I just tried that, didn't work, same error. declare @test varchar(1000) set @test = '' SELECT description, (SELECT @test = @test + FirstName FROM EMP_tblEmployee ) as testing from EMP_tblCrew – Since_2008 Dec 10 '10 at 16:43
If you really want to concatenate names you might think about using a cursor or some CTE function. Please explain what you want to achieve. Better yet, mock up a result set of what you want. – Paul Sasik Dec 10 '10 at 16:44
You're missing the FROM clause in your comment's query. – Paul Sasik Dec 10 '10 at 16:45
Sorry, that was a copy paste issue (was taking out the db names when posting here). – Since_2008 Dec 10 '10 at 16:46
Updated the query to use FOR XML PATH('') for concatenation. Note that the variable is no longer needed. – Paul Sasik Dec 10 '10 at 16:49

score 1 · Answer 2 · answered Dec 10 '10 at 16:45

1

Try searching google for "SQL Concatenate rows into string". There are a number of useful solutions for this.

It looks like you also need to join the employee to the crew table, so that you dont get some cartesian product (usually not what is wanted).

answered Dec 10 '10 at 16:45

StingyJack

19,041
10
63
122

This is embarrassing, I found a couple of questions similar to mine using your search criteria... Thanks for the input. http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string http://stackoverflow.com/questions/6899/is-there-a-way-to-create-a-mssql-function-to-join-multiple-rows-from-a-subquery – Since_2008 Dec 10 '10 at 16:59

score 0 · Answer 3 · edited Aug 29 '13 at 17:16

Probably the easiest path involves using a recursive CTE (common table expression). A detailed example of that is at https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Note, this basically requires that you have sql 2008.

Another path would be to create a user defined function that returned the concatenated values from the EMP_tblEmployee table. You could do this in 2005 or 2008.

T-SQL, Select @variable within a sub-query causes syntax error

3 Answers3