T-SQL

• balu.arunkumar

  Ten Centuries

  Points: 1132

  More actions

  April 15, 2018 at 1:41 am

  #337161

  Hi,
  How below query works? Can any one explain?create table #temp1(id int,name varchar(10))

  insert into #temp1
select 1,'A'
union
select 1,'B'
union
select 1,'C'

  declare @column varchar(1000)
select @column=ISNULL(@column,'')+name+',' from #temp1
select top 1 id,stuff(@column,len(@column),1,'') from #temp1


• Thom A

  SSC Guru

  Points: 99133

  More actions

  April 15, 2018 at 5:32 am

  #1986864

  What do you mean, "works"?

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Jeff Moden

  SSC Guru

  Points: 1004402

  More actions

  April 15, 2018 at 6:33 am

  #1986867

  balu.arunkumar - Sunday, April 15, 2018 1:41 AM

  Hi,
  How below query works? Can any one explain?create table #temp1(id int,name varchar(10))

  insert into #temp1
  select 1,'A'
  union
  select 1,'B'
  union
  select 1,'C'

  declare @column varchar(1000)
select @column=ISNULL(@column,'')+name+',' from #temp1
select top 1 id,stuff(@column,len(@column),1,'') from #temp1


  It's an "overloading" of a variable to do concatenation.  SQL Server is one of the few RDBMSs where it actually works (Oracle won't allow it, for example).

  Every SELECT is actually a loop behind the scenes.  It's very fast (near machine language levels) but it's still a loop.  So, the pseudo code for the your code would be the following.

  1. Declare an "accumulator" variable to build the result in.  It starts out as NULL.
  2. Read a row (value) from the table and , using string concatenation, add it to the variable.  Since the variable starts out as NULL, we use ISNULL to substitute an empty string for the variable on the first row only.  This step also adds a trailing comma as a delimiter to the value that we're concatenating to the variable.
  3.  If there are any more rows left, jump back to step 2 to read the next row in the table.
  4.  If there are no more rows left, remove the trailing comma from the contents of the variable by using STUFF to replace the last character in the variable with an empty string.

  There are some caveats to using this method and you'll sometimes find that it will return an empty string or a truncated string depending on some conditions that can be found in articles that I don't have the links for.

  Most will agree that it's better to use something like that found in the following article but, beware, that also has some serious caveats.
  http://www.sqlservercentral.com/articles/comma+separated+list/71700/

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)