SQL SERVER 2005/2008: Concatenation of column data using ‘FOR XML’

Concatenation of a column data, say for table TAB1.  Yaa! its easy..

Select convert(varchar,col1) +’ ’+ convert(varchar,col1)+’ ’ + convert(varchar,col1) from TAB1 

Hmm..! but that’s not what i am talking about. Say you have table temp2 with columns [d] and [e].

[d] is repeated multiple times for different values of  [e] . Now, to add more complexity [d] is foreign key to column [a] in table temp1.
yaa temp1 [1---M] temp2 .Now temp1 also has column [b].

Following are contents of both tables :


Now. the requirement, you need rowset { temp1.[a]   temp2[Concatenated e] }.

Q : How to do it?..
A1 : Write a table values function that returns concatenated [e] and [b] for temp2, then join this output with temp1 for the required rowset.
A2: write complex CTE for [e] and concatenated [b] in temp2  and join it with temp1  for required rowset.

I know this two easy solutions. There might be other approaches too. But what we are going to look at is ‘FOR XML PATH’.

• 
  

  know more about ‘FOR XML’ in SQL SERVER 2005 here

  I found lot of resources that explained same thing, but in too complex way. the simplest and best i found was here.

I am further making it more easier, i guess…! :) I have created small snippet for explain the scenario i had mentioned prior in this post and it’s solution. Following snippet is created in SQL SERVER 2008.

Copy paste following code in your management studio query window and observe the results :)

• 1st result is contents of temp1
  
  
• 2nd result is content of temp2
  
  
• 3rd result is concatenation of column [b] in temp1 - - - additional example not discussed above.
  
  
• 4th result is for scenario i am talking about.
  

  BEGIN
  CREATE TABLE temp1
  (
  a  int IDENTITY(1,1)  PRIMARY KEY ,
  b  varchar(10)
  )
  CREATE TABLE temp2
  (
  d  int ,
  e varchar(5)
  )
  ALTER TABLE temp2
  ADD CONSTRAINT tempp 
  FOREIGN KEY(d)REFERENCES temp1(a)
  
  insert into temp1 values (1)
  insert into temp1 values (2)
  insert into temp1 values (3)
  insert into temp1 values (4)
  insert into temp1 values (11)
  insert into temp1 values (5)
  insert into temp1 values (1)
  insert into temp1 values (3)
  insert into temp1 values (2)
  insert into temp1 values (4)
  insert into temp1 values (12)
  
  insert into temp2 values (1,'fdg')
  insert into temp2 values (2,'ghg')
  insert into temp2 values (3,'hgh')
  insert into temp2 values (4,'l;kl')
  insert into temp2 values (11,'gfh')
  insert into temp2 values (5,'rtr')
  insert into temp2 values (1,'khgj')
  insert into temp2 values (3,'dfd')
  insert into temp2 values (2,'ghfg')
  insert into temp2 values (4,'fghfg')
  insert into temp2 values (4,'vishu')
  insert into temp2 values (1,'opop')
  
  --1st table
  Select * from temp1
  --second table
  Select * from temp2
  
  --Get comma separated string of the whole column (only one column) from table using fox xml path
  Select top 1 stuff(b,1,2,'')
  from 
  (
  SELECT     
  (    SELECT 
  ', '+b
  FROM temp1 
  FOR XML PATH('') 
  ) as b 
  FROM temp1 
  ) T
  --following query gives result for the scenario i am talking about 
  Select
  a,
  stuff(
  coalesce((    SELECT     ' ~ '+e
  FROM temp2
  where a=d
  FOR XML PATH('') 
  ), '---')
  ,1,3,''
  )as e 
  from temp1     
  DROP table temp2
  drop table temp1
  END
  

  
  
  
  
  

  Let me know if it helps you.. Would appreciate if you share your knowledge and experience about same.