What is happening in this T-SQL code? (Concatenting the results of a SELECT statement)
Posted
by Ben McCormack
on Stack Overflow
See other posts from Stack Overflow
or by Ben McCormack
Published on 2010-03-31T18:42:17Z
Indexed on
2010/03/31
19:23 UTC
Read the original article
Hit count: 296
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?
© Stack Overflow or respective owner