What is happening in this T-SQL code? (Concatenting the results of a SELECT statement)

Question

I'm just starting to learn T-SQL and could use some help in understanding what's going on in a particular block of code. I modified some code in an answer I received in a previous question, and here is the code in question:

DECLARE @column_list AS varchar(max) 
SELECT @column_list = COALESCE(@column_list, ',') + 
    'SUM(Case When Sku2=' + CONVERT(varchar, Sku2) + 
    ' Then Quantity Else 0 End) As [' + 
    CONVERT(varchar, Sku2) + ' - ' + 
    Convert(varchar,Description) +'],'
FROM OrderDetailDeliveryReview 
Inner Join InvMast on SKU2 = SKU and LocationTypeID=4
GROUP BY Sku2 , Description
ORDER BY Sku2 

Set @column_list = Left(@column_list,Len(@column_list)-1)

Select @column_list

----------------------------------------

1 row is returned:
,SUM(Case When Sku2=157 Then Quantity Else 0 End) As [157 -..., SUM(Case ...

The T-SQL code does exactly what I want, which is to make a single result based on the results of a query, which will then be used in another query.

However, I can't figure out how the SELECT @column_list =... statement is putting multiple values into a single string of characters by being inside a SELECT statement. Without the assignment to @column_list, the SELECT statement would simply return multiple rows. How is it that by having the variable within the SELECT statement that the results get "flattened" down into one value? How should I read this T-SQL to properly understand what's going on?

Carolinecarolingian

Answer 1

In SQL Server:

SELECT @var = @var + col
FROM TABLE

actually concatenates the values. It's a quirks mode (and I am unable at this time to find a reference to the documentation of feature - which has been used for years in the SQL Server community). If @var is NULL at the start (i.e. an uninitialized value), then you need a COALESCE or ISNULL (and you'll often use a separator):

SELECT @var = ISNULL(@var, '') + col + '|'
FROM TABLE

or this to make a comma-separated list, and then remove only the leading comma:

SELECT @var = ISNULL(@var, '') + ',' + col
FROM TABLE

SET @var = STUFF(@var, 1, 1, '')

or (courtesy of KM, relying on NULL + ',' yielding NULL to eliminate the need for STUFF for the first item in the list):

SELECT @var = ISNULL(@var + ',', '') + col
FROM TABLE 

or this to make a list with a leading, separated and trailing comma:

SELECT @var = ISNULL(@var, ',') + col + ','
FROM TABLE

Piotr

don't need STUFF() when you do it this way: SELECT @var=ISNULL(@var+',','')+col FROM TABLE – Propellant

@KM, yeah, let me add that technique. – Piotr

it is probably more efficient to default @var to empty string: '' and eliminate the ISNULL() done for each row and just do the STUFF one time at the very end, though... – Propellant

Excellect answer. Thanks for your explanation. This is going to help a ton! – Carolinecarolingian

Answer 2

You will want to look into the COALESCE function. A good article describing what is happening can be seen here.

Budworth

@Budworth - Looking into the COALESCE function certainly helped. What's is that without the COALESCE function, NULL is returned. Is this because character strings cannot be added to NULL values? – Carolinecarolingian

Hi Ben, anything added to a null will always result in a null – Budworth

@Ben McCormack ANSI NULL behavior will give NULL + str -> NULL. Non-ANSI behavior will give NULL + str -> str - you can get this non-standard behavior with SET CONCAT_NULL_YIELDS_NULL OFF. – Piotr

@Cade Roux Thanks for adding another comment after mine, I never knew about that! – Budworth