T-sql, how does it work? SELECT @MyList = ISNULL (@MyList, '') + Title + ',' FROM Titles

Question

I came across a very cool t-sql to create a comma-separated list of column values ​​from selected rows in a single t-sql query:

SELECT @MyList = ISNULL(@MyList,'') + Title + ', ' FROM Titles 

But I can’t understand how this works. One way or another, it should make a recursive call, but I don't know how to do it. Can someone explain this to me or send me a link that explains this? To see how this works, use the following script:

 CREATE TABLE Titles( Title varchar(50) ) insert Titles values ( 'Doctor') insert Titles values ( 'Nurse') insert Titles values ( 'Administrator') insert Titles values ( 'CMA') select * from Titles DECLARE @MyList VARCHAR(1000) SET @MyList = '' SELECT @MyList = ISNULL(@MyList,'') + Title + ', ' FROM Titles SELECT @MyList 

Answer 1

Appointment:

 @MyList = ISNULL(@MyList,'') + Title + ', ' 

evaluated for each row of the Titles table. It concatenates each value of the Title column in the @MyList column.

Testing ISNULL(@MyList,'') is only necessary, so @MyList starts with an empty string if it is NULL . In your example, ISNULL not required because @MyList is explicitly set to an empty string.

Answer 2

Other answers explained why this creates a comma-separated list. You will notice that at the end you will get an extra comma, which you can remove after that if you want. If you are using SQL Server 2005 or later, you can use COALESCE and not have this comma:

 SELECT @MyList = COALESCE(@MyList + ', ','') + Title FROM Titles 

For the first line, @MyList will be NULL , so @MyList + ', ' will be evaluated to NULL , and COALESCE will return. '' Essentially, processing the first line does this:

SELECT @MyList = '' + Title

For subsequent lines, COALESCE will return @MyList + ', ' and you will get the equivalent

SELECT @MyList = @MyList + ', ' + Title .

Answer 3

The Titles table contains a set of predefined names, so the query

 SELECT @MyList = ISNULL(@MyList,'') + Title + ', ' FROM Titles 

Does it add a column header value for each row in the Titles table.

This is equivalent to the following loop:

 // Actually ISNULL(@MyList,'') does initialize // @MyList by empty string whilst processing the first row in tsql query string myList = ""; foreach(string title in Titles) { myList = myList + ', ' + title; }