March 6, 2014 at 11:56 pm
I googled this code, I cant understand how this convert entire column into single row..
how this achieves without loop ?
Any help would be great to understand.
select top 5 name into #tbl from sysobjects
select * from #tbl
Declare @list varchar(max)
select @list = coalesce (@list,'')+name+',' from #tbl
select @list
drop table #tbl
March 7, 2014 at 12:18 am
COALESCE doesn't really do anything in this code, except converting NULL values to empty strings.
If COALESCE would be omitted from the code and there would be one NULL value for the name column, the entire result would be NULL.
(and also because they don't initialize the @list variable, so it's initial value is NULL)
The real "magic" here is that a variable assignment is put in a SELECT statement that returns multiple rows. (@list = @list + name)
This simulates some sort of loop that will assign each row value to the value of the previous row.
row 1: @list = '' + name1
row 2: @list = @list + name2 = '' + name1 + name2
row 3: @list = @list + name3 = '' + name1 + name2 + name3
and so on.
The code is written poorly (in my opinion).
This would be a better start (using SQL Server 2012), as it accounts for NULL in all forms.
SELECT TOP 5 name
INTO #tbl
FROM sysobjects;
--select * from #tbl
DECLARE @list VARCHAR(700);
SELECT @list = CONCAT(@list,name,',')
FROM #tbl;
SELECT STUFF(@list,LEN(@list),1,''); -- remove comma on the right
DROP TABLE #tbl;
However, using variable concatenation depends on the physical implementation of SQL Server.
To quote a KB article:
The correct behavior for an aggregate concatenation query is undefined.
http://support.microsoft.com/kb/287515/en-us
So you better avoid this method.
There are plenty of alternatives:
Concatenating Row Values in Transact-SQL[/url]
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply