Concatenate Strings in a COLUMN using comma delimiter, I need some nice code.

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  September 18, 2018 at 11:14 am

  #363701

  Please run my code and you will see what I mean.
  So I like to concatenate values in a column and get one row of output.
  The solution below works.. But the column name it gives has a wierd guid.
  How ca we chane that column name

  
if object_id('tempdb..#t') IS NOT NULL DROP TABLE #t;

  Create table #t(mm VARCHAR(100) )

  INSERT INTO #t( mm) VALUES ( 'ABC' );
INSERT INTO #t( mm) VALUES ( 'DDD' );
INSERT INTO #t( mm) VALUES ( 'XXX' );
INSERT INTO #t( mm) VALUES ( 'YYY' );

  Select * FROM #t;

  --Instead of 4 rows I would like one line and the
--output should look like
--ABC,DDD,XXX,YYY

  -- I Know there is a some nice way to do this using XML SYNTAX
-- and without using the CONCATENATE FUNCTION
--The following work but gives a wierd column name.
--How can we rename the column name to something readable ?

  SELECT ',' + mm AS [text()]
FROM #t
FOR XML PATH('')

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  September 18, 2018 at 11:22 am

  #2006174

  mw_sql_developer - Tuesday, September 18, 2018 11:14 AM

  Please run my code and you will see what I mean.
  So I like to concatenate values in a column and get one row of output.
  The solution below works.. But the column name it gives has a wierd guid.
  How ca we chane that column name

  
if object_id('tempdb..#t') IS NOT NULL DROP TABLE #t;

  Create table #t(mm VARCHAR(100) )

  INSERT INTO #t( mm) VALUES ( 'ABC' );
INSERT INTO #t( mm) VALUES ( 'DDD' );
INSERT INTO #t( mm) VALUES ( 'XXX' );
INSERT INTO #t( mm) VALUES ( 'YYY' );

  Select * FROM #t;

  --Instead of 4 rows I would like one line and the
--output should look like
--ABC,DDD,XXX,YYY

  -- I Know there is a some nice way to do this using XML SYNTAX
-- and without using the CONCATENATE FUNCTION
--The following work but gives a wierd column name.
--How can we rename the column name to something readable ?

  SELECT ',' + mm AS [text()]
FROM #t
FOR XML PATH('')

  Try:
  
select stuff((select ',' + mm
     from #t
     for xml path(''),TYPE).value('.','varchar(max)'),1,1,'');


• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  November 2, 2018 at 9:52 am

  #2012217

  Folks.. No need to reply... All set, I just modified the code a bit , it works.. Allows you to group by ID

  if object_id('tempdb..#t') IS NOT NULL DROP TABLE #t;

  Create table #t(mm VARCHAR(100), ID int )

  INSERT INTO #t( mm, ID) VALUES ( 'ABC',1 );
INSERT INTO #t( mm, ID) VALUES ( 'DDD',2 );
INSERT INTO #t( mm, ID) VALUES ( 'XXX',2 );
INSERT INTO #t( mm, ID) VALUES ( 'YYY',1 );

  --Instead of 4 rows I would like one line and the
--output should look like
--ABC,DDD,XXX,YYY

  -- I Know there is a some nice way to do this using XML SYNTAX
-- and without using the CONCATENATE FUNCTION
--The following work but gives a wierd column name.
--How can we rename the column name to something readable ?

  --SELECT ',' + mm AS [text()]
--FROM #t
--FOR XML PATH('')

  SELECT ID, abc = STUFF(
     (SELECT ',' + mm
     FROM #t t1
     WHERE t1.id = t2.id
     FOR XML PATH (''))
     , 1, 1, '') from #t t2
group by id;

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  November 4, 2018 at 4:53 pm

  #2012353

  mw_sql_developer - Friday, November 2, 2018 9:52 AM

  Folks.. No need to reply... All set, I just modified the code a bit , it works.. Allows you to group by ID

  if object_id('tempdb..#t') IS NOT NULL DROP TABLE #t;

  Create table #t(mm VARCHAR(100), ID int )

  INSERT INTO #t( mm, ID) VALUES ( 'ABC',1 );
INSERT INTO #t( mm, ID) VALUES ( 'DDD',2 );
INSERT INTO #t( mm, ID) VALUES ( 'XXX',2 );
INSERT INTO #t( mm, ID) VALUES ( 'YYY',1 );

  --Instead of 4 rows I would like one line and the
--output should look like
--ABC,DDD,XXX,YYY

  -- I Know there is a some nice way to do this using XML SYNTAX
-- and without using the CONCATENATE FUNCTION
--The following work but gives a wierd column name.
--How can we rename the column name to something readable ?

  --SELECT ',' + mm AS [text()]
--FROM #t
--FOR XML PATH('')

  SELECT ID, abc = STUFF(
     (SELECT ',' + mm
     FROM #t t1
     WHERE t1.id = t2.id
     FOR XML PATH (''))
     , 1, 1, '') from #t t2
group by id;


  Heh... now wait for when an XML reserved character pops up in the data. 😉  You can overcome that with TYPE but I'll let you look that up.

  --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)
  

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  November 5, 2018 at 1:48 am

  #2012366

  Lynn Pettis - Tuesday, September 18, 2018 11:22 AM

  mw_sql_developer - Tuesday, September 18, 2018 11:14 AM

  Please run my code and you will see what I mean.
  So I like to concatenate values in a column and get one row of output.
  The solution below works.. But the column name it gives has a wierd guid.
  How ca we chane that column name

  
if object_id('tempdb..#t') IS NOT NULL DROP TABLE #t;

  Create table #t(mm VARCHAR(100) )

  INSERT INTO #t( mm) VALUES ( 'ABC' );
INSERT INTO #t( mm) VALUES ( 'DDD' );
INSERT INTO #t( mm) VALUES ( 'XXX' );
INSERT INTO #t( mm) VALUES ( 'YYY' );

  Select * FROM #t;

  --Instead of 4 rows I would like one line and the
--output should look like
--ABC,DDD,XXX,YYY

  -- I Know there is a some nice way to do this using XML SYNTAX
-- and without using the CONCATENATE FUNCTION
--The following work but gives a wierd column name.
--How can we rename the column name to something readable ?

  SELECT ',' + mm AS [text()]
FROM #t
FOR XML PATH('')

  Try:
  
select stuff((select ',' + mm
     from #t
     for xml path(''),TYPE).value('.','varchar(max)'),1,1,'');


  Always use the text() function for this, prevents the reconstruct of the returned set as XML.
  😎

  
select stuff((select ',' + mm
  from #t
  for xml path(''),TYPE).value('(./text())[1]','varchar(max)'),1,1,'');

  


• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  November 5, 2018 at 9:00 am

  #2012407

  Eirikur Eiriksson - Monday, November 5, 2018 1:48 AM

  Lynn Pettis - Tuesday, September 18, 2018 11:22 AM

  mw_sql_developer - Tuesday, September 18, 2018 11:14 AM

  Please run my code and you will see what I mean.
  So I like to concatenate values in a column and get one row of output.
  The solution below works.. But the column name it gives has a wierd guid.
  How ca we chane that column name

  
if object_id('tempdb..#t') IS NOT NULL DROP TABLE #t;

  Create table #t(mm VARCHAR(100) )

  INSERT INTO #t( mm) VALUES ( 'ABC' );
INSERT INTO #t( mm) VALUES ( 'DDD' );
INSERT INTO #t( mm) VALUES ( 'XXX' );
INSERT INTO #t( mm) VALUES ( 'YYY' );

  Select * FROM #t;

  --Instead of 4 rows I would like one line and the
--output should look like
--ABC,DDD,XXX,YYY

  -- I Know there is a some nice way to do this using XML SYNTAX
-- and without using the CONCATENATE FUNCTION
--The following work but gives a wierd column name.
--How can we rename the column name to something readable ?

  SELECT ',' + mm AS [text()]
FROM #t
FOR XML PATH('')

  Try:
  
select stuff((select ',' + mm
     from #t
     for xml path(''),TYPE).value('.','varchar(max)'),1,1,'');


  Always use the text() function for this, prevents the reconstruct of the returned set as XML.
  😎

  
select stuff((select ',' + mm
  from #t
  for xml path(''),TYPE).value('(./text())[1]','varchar(max)'),1,1,'');

  


  Lynn:
  if you don;t mind can you help me with adding that TYPE to the following SQL please ?

  
SELECT ID, abc = STUFF(
  (SELECT ',' + mm
  FROM #t t1
  WHERE t1.id = t2.id
  FOR XML PATH (''))
  , 1, 1, '') from #t t2
group by id;


• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  November 5, 2018 at 9:05 am

  #2012409

  mw_sql_developer - Monday, November 5, 2018 9:00 AM

  Eirikur Eiriksson - Monday, November 5, 2018 1:48 AM

  Lynn Pettis - Tuesday, September 18, 2018 11:22 AM

  mw_sql_developer - Tuesday, September 18, 2018 11:14 AM

  Please run my code and you will see what I mean.
  So I like to concatenate values in a column and get one row of output.
  The solution below works.. But the column name it gives has a wierd guid.
  How ca we chane that column name

  
if object_id('tempdb..#t') IS NOT NULL DROP TABLE #t;

  Create table #t(mm VARCHAR(100) )

  INSERT INTO #t( mm) VALUES ( 'ABC' );
INSERT INTO #t( mm) VALUES ( 'DDD' );
INSERT INTO #t( mm) VALUES ( 'XXX' );
INSERT INTO #t( mm) VALUES ( 'YYY' );

  Select * FROM #t;

  --Instead of 4 rows I would like one line and the
--output should look like
--ABC,DDD,XXX,YYY

  -- I Know there is a some nice way to do this using XML SYNTAX
-- and without using the CONCATENATE FUNCTION
--The following work but gives a wierd column name.
--How can we rename the column name to something readable ?

  SELECT ',' + mm AS [text()]
FROM #t
FOR XML PATH('')

  Try:
  
select stuff((select ',' + mm
     from #t
     for xml path(''),TYPE).value('.','varchar(max)'),1,1,'');


  Always use the text() function for this, prevents the reconstruct of the returned set as XML.
  😎

  
select stuff((select ',' + mm
  from #t
  for xml path(''),TYPE).value('(./text())[1]','varchar(max)'),1,1,'');

  


  Lynn:
  if you don;t mind can you help me with adding that TYPE to the following SQL please ?

  
SELECT ID, abc = STUFF(
  (SELECT ',' + mm
  FROM #t t1
  WHERE t1.id = t2.id
  FOR XML PATH (''))
  , 1, 1, '') from #t t2
group by id;


  Sorry , no need, I just figured out.. So is this the solution that Jeff M was talking about.....
  So by using the TYPE you are safe dealing with data that has XML reserved characters

  if object_id('tempdb..#t') IS NOT NULL DROP TABLE #t;

  Create table #t(mm VARCHAR(100), ID int )

  INSERT INTO #t( mm, ID) VALUES ( 'ABC',1 );
INSERT INTO #t( mm, ID) VALUES ( 'DDD',2 );
INSERT INTO #t( mm, ID) VALUES ( 'XXX',2 );
INSERT INTO #t( mm, ID) VALUES ( 'YYY',1 );

  SELECT ID, abc = STUFF(
  (SELECT ',' + mm
  FROM #t t1
  WHERE t1.id = t2.id
  FOR xml path(''),TYPE).value('(./text())[1]','varchar(max)'),1,1,'') from #t t2
group by id;

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  November 5, 2018 at 9:19 am

  #2012412

  mw_sql_developer - Monday, November 5, 2018 9:05 AM

  mw_sql_developer - Monday, November 5, 2018 9:00 AM

  Eirikur Eiriksson - Monday, November 5, 2018 1:48 AM

  Lynn Pettis - Tuesday, September 18, 2018 11:22 AM

  mw_sql_developer - Tuesday, September 18, 2018 11:14 AM

  Please run my code and you will see what I mean.
  So I like to concatenate values in a column and get one row of output.
  The solution below works.. But the column name it gives has a wierd guid.
  How ca we chane that column name

  
if object_id('tempdb..#t') IS NOT NULL DROP TABLE #t;

  Create table #t(mm VARCHAR(100) )

  INSERT INTO #t( mm) VALUES ( 'ABC' );
INSERT INTO #t( mm) VALUES ( 'DDD' );
INSERT INTO #t( mm) VALUES ( 'XXX' );
INSERT INTO #t( mm) VALUES ( 'YYY' );

  Select * FROM #t;

  --Instead of 4 rows I would like one line and the
--output should look like
--ABC,DDD,XXX,YYY

  -- I Know there is a some nice way to do this using XML SYNTAX
-- and without using the CONCATENATE FUNCTION
--The following work but gives a wierd column name.
--How can we rename the column name to something readable ?

  SELECT ',' + mm AS [text()]
FROM #t
FOR XML PATH('')

  Try:
  
select stuff((select ',' + mm
     from #t
     for xml path(''),TYPE).value('.','varchar(max)'),1,1,'');


  Always use the text() function for this, prevents the reconstruct of the returned set as XML.
  😎

  
select stuff((select ',' + mm
  from #t
  for xml path(''),TYPE).value('(./text())[1]','varchar(max)'),1,1,'');

  


  Lynn:
  if you don;t mind can you help me with adding that TYPE to the following SQL please ?

  
SELECT ID, abc = STUFF(
  (SELECT ',' + mm
  FROM #t t1
  WHERE t1.id = t2.id
  FOR XML PATH (''))
  , 1, 1, '') from #t t2
group by id;


  Sorry , no need, I just figured out.. So is this the solution that Jeff M was talking about.....
  So by using the TYPE you are safe dealing with data that has XML reserved characters

  if object_id('tempdb..#t') IS NOT NULL DROP TABLE #t;

  Create table #t(mm VARCHAR(100), ID int )

  INSERT INTO #t( mm, ID) VALUES ( 'ABC',1 );
INSERT INTO #t( mm, ID) VALUES ( 'DDD',2 );
INSERT INTO #t( mm, ID) VALUES ( 'XXX',2 );
INSERT INTO #t( mm, ID) VALUES ( 'YYY',1 );

  SELECT ID, abc = STUFF(
  (SELECT ',' + mm
  FROM #t t1
  WHERE t1.id = t2.id
  FOR xml path(''),TYPE).value('(./text())[1]','varchar(max)'),1,1,'') from #t t2
group by id;

  If object_id('tempdb..#t') IS NOT NULL DROP TABLE #t;

  Create table #t(mm VARCHAR(100), ID int )

  INSERT INTO #t( mm, ID) VALUES ( 'AB<C',1 );
INSERT INTO #t( mm, ID) VALUES ( '<!=D>DD',2 );
INSERT INTO #t( mm, ID) VALUES ( 'XXX',2 );
INSERT INTO #t( mm, ID) VALUES ( 'YYY',1 );

  --Handles XML reserved characters well!
SELECT ID, abc = STUFF(
  (SELECT ',' + mm
  FROM #t t1
  WHERE t1.id = t2.id
  FOR xml path(''),TYPE).value('(./text())[1]','varchar(max)'),1,1,'') from #t t2
group by id;

  --Fails
SELECT ID, abc = STUFF(
  (SELECT ',' + mm
  FROM #t t1
  WHERE t1.id = t2.id
  FOR XML PATH (''))
  , 1, 1, '') from #t t2
group by id;

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  November 5, 2018 at 9:38 am

  #2012418

  mw_sql_developer - Monday, November 5, 2018 9:19 AM

  mw_sql_developer - Monday, November 5, 2018 9:05 AM

  mw_sql_developer - Monday, November 5, 2018 9:00 AM

  Eirikur Eiriksson - Monday, November 5, 2018 1:48 AM

  Lynn Pettis - Tuesday, September 18, 2018 11:22 AM

  mw_sql_developer - Tuesday, September 18, 2018 11:14 AM

  Please run my code and you will see what I mean.
  So I like to concatenate values in a column and get one row of output.
  The solution below works.. But the column name it gives has a wierd guid.
  How ca we chane that column name

  
if object_id('tempdb..#t') IS NOT NULL DROP TABLE #t;

  Create table #t(mm VARCHAR(100) )

  INSERT INTO #t( mm) VALUES ( 'ABC' );
INSERT INTO #t( mm) VALUES ( 'DDD' );
INSERT INTO #t( mm) VALUES ( 'XXX' );
INSERT INTO #t( mm) VALUES ( 'YYY' );

  Select * FROM #t;

  --Instead of 4 rows I would like one line and the
--output should look like
--ABC,DDD,XXX,YYY

  -- I Know there is a some nice way to do this using XML SYNTAX
-- and without using the CONCATENATE FUNCTION
--The following work but gives a wierd column name.
--How can we rename the column name to something readable ?

  SELECT ',' + mm AS [text()]
FROM #t
FOR XML PATH('')

  Try:
  
select stuff((select ',' + mm
     from #t
     for xml path(''),TYPE).value('.','varchar(max)'),1,1,'');


  Always use the text() function for this, prevents the reconstruct of the returned set as XML.
  😎

  
select stuff((select ',' + mm
  from #t
  for xml path(''),TYPE).value('(./text())[1]','varchar(max)'),1,1,'');

  


  Lynn:
  if you don;t mind can you help me with adding that TYPE to the following SQL please ?

  
SELECT ID, abc = STUFF(
  (SELECT ',' + mm
  FROM #t t1
  WHERE t1.id = t2.id
  FOR XML PATH (''))
  , 1, 1, '') from #t t2
group by id;


  Sorry , no need, I just figured out.. So is this the solution that Jeff M was talking about.....
  So by using the TYPE you are safe dealing with data that has XML reserved characters

  if object_id('tempdb..#t') IS NOT NULL DROP TABLE #t;

  Create table #t(mm VARCHAR(100), ID int )

  INSERT INTO #t( mm, ID) VALUES ( 'ABC',1 );
INSERT INTO #t( mm, ID) VALUES ( 'DDD',2 );
INSERT INTO #t( mm, ID) VALUES ( 'XXX',2 );
INSERT INTO #t( mm, ID) VALUES ( 'YYY',1 );

  SELECT ID, abc = STUFF(
  (SELECT ',' + mm
  FROM #t t1
  WHERE t1.id = t2.id
  FOR xml path(''),TYPE).value('(./text())[1]','varchar(max)'),1,1,'') from #t t2
group by id;

  If object_id('tempdb..#t') IS NOT NULL DROP TABLE #t;

  Create table #t(mm VARCHAR(100), ID int )

  INSERT INTO #t( mm, ID) VALUES ( 'AB<C',1 );
INSERT INTO #t( mm, ID) VALUES ( '<!=D>DD',2 );
INSERT INTO #t( mm, ID) VALUES ( 'XXX',2 );
INSERT INTO #t( mm, ID) VALUES ( 'YYY',1 );

  --Handles XML reserved characters well!
SELECT ID, abc = STUFF(
  (SELECT ',' + mm
  FROM #t t1
  WHERE t1.id = t2.id
  FOR xml path(''),TYPE).value('(./text())[1]','varchar(max)'),1,1,'') from #t t2
group by id;

  --Fails
SELECT ID, abc = STUFF(
  (SELECT ',' + mm
  FROM #t t1
  WHERE t1.id = t2.id
  FOR XML PATH (''))
  , 1, 1, '') from #t t2
group by id;

  What are you going on about? I've demonstrated how to do this.
  😎

  You need to understand how this works.

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  November 5, 2018 at 11:10 am

  #2012433

  Eirikur Eiriksson - Monday, November 5, 2018 9:38 AM

  mw_sql_developer - Monday, November 5, 2018 9:19 AM

  mw_sql_developer - Monday, November 5, 2018 9:05 AM

  mw_sql_developer - Monday, November 5, 2018 9:00 AM

  Eirikur Eiriksson - Monday, November 5, 2018 1:48 AM

  Lynn Pettis - Tuesday, September 18, 2018 11:22 AM

  mw_sql_developer - Tuesday, September 18, 2018 11:14 AM

  Please run my code and you will see what I mean.
  So I like to concatenate values in a column and get one row of output.
  The solution below works.. But the column name it gives has a wierd guid.
  How ca we chane that column name

  
if object_id('tempdb..#t') IS NOT NULL DROP TABLE #t;

  Create table #t(mm VARCHAR(100) )

  INSERT INTO #t( mm) VALUES ( 'ABC' );
INSERT INTO #t( mm) VALUES ( 'DDD' );
INSERT INTO #t( mm) VALUES ( 'XXX' );
INSERT INTO #t( mm) VALUES ( 'YYY' );

  Select * FROM #t;

  --Instead of 4 rows I would like one line and the
--output should look like
--ABC,DDD,XXX,YYY

  -- I Know there is a some nice way to do this using XML SYNTAX
-- and without using the CONCATENATE FUNCTION
--The following work but gives a wierd column name.
--How can we rename the column name to something readable ?

  SELECT ',' + mm AS [text()]
FROM #t
FOR XML PATH('')

  Try:
  
select stuff((select ',' + mm
     from #t
     for xml path(''),TYPE).value('.','varchar(max)'),1,1,'');


  Always use the text() function for this, prevents the reconstruct of the returned set as XML.
  😎

  
select stuff((select ',' + mm
  from #t
  for xml path(''),TYPE).value('(./text())[1]','varchar(max)'),1,1,'');

  


  Lynn:
  if you don;t mind can you help me with adding that TYPE to the following SQL please ?

  
SELECT ID, abc = STUFF(
  (SELECT ',' + mm
  FROM #t t1
  WHERE t1.id = t2.id
  FOR XML PATH (''))
  , 1, 1, '') from #t t2
group by id;


  Sorry , no need, I just figured out.. So is this the solution that Jeff M was talking about.....
  So by using the TYPE you are safe dealing with data that has XML reserved characters

  if object_id('tempdb..#t') IS NOT NULL DROP TABLE #t;

  Create table #t(mm VARCHAR(100), ID int )

  INSERT INTO #t( mm, ID) VALUES ( 'ABC',1 );
INSERT INTO #t( mm, ID) VALUES ( 'DDD',2 );
INSERT INTO #t( mm, ID) VALUES ( 'XXX',2 );
INSERT INTO #t( mm, ID) VALUES ( 'YYY',1 );

  SELECT ID, abc = STUFF(
  (SELECT ',' + mm
  FROM #t t1
  WHERE t1.id = t2.id
  FOR xml path(''),TYPE).value('(./text())[1]','varchar(max)'),1,1,'') from #t t2
group by id;

  If object_id('tempdb..#t') IS NOT NULL DROP TABLE #t;

  Create table #t(mm VARCHAR(100), ID int )

  INSERT INTO #t( mm, ID) VALUES ( 'AB<C',1 );
INSERT INTO #t( mm, ID) VALUES ( '<!=D>DD',2 );
INSERT INTO #t( mm, ID) VALUES ( 'XXX',2 );
INSERT INTO #t( mm, ID) VALUES ( 'YYY',1 );

  --Handles XML reserved characters well!
SELECT ID, abc = STUFF(
  (SELECT ',' + mm
  FROM #t t1
  WHERE t1.id = t2.id
  FOR xml path(''),TYPE).value('(./text())[1]','varchar(max)'),1,1,'') from #t t2
group by id;

  --Fails
SELECT ID, abc = STUFF(
  (SELECT ',' + mm
  FROM #t t1
  WHERE t1.id = t2.id
  FOR XML PATH (''))
  , 1, 1, '') from #t t2
group by id;

  What are you going on about? I've demonstrated how to do this.
  😎

  You need to understand how this works.

  Thank you for help Lynn, Yep, I understand after experimenting. Yes  😎

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 5, 2018 at 4:18 pm

  #2012462

  mw_sql_developer - Monday, November 5, 2018 11:10 AM

  Eirikur Eiriksson - Monday, November 5, 2018 9:38 AM

  mw_sql_developer - Monday, November 5, 2018 9:19 AM

  mw_sql_developer - Monday, November 5, 2018 9:05 AM

  mw_sql_developer - Monday, November 5, 2018 9:00 AM

  Eirikur Eiriksson - Monday, November 5, 2018 1:48 AM

  Lynn Pettis - Tuesday, September 18, 2018 11:22 AM

  mw_sql_developer - Tuesday, September 18, 2018 11:14 AM

  Please run my code and you will see what I mean.
  So I like to concatenate values in a column and get one row of output.
  The solution below works.. But the column name it gives has a wierd guid.
  How ca we chane that column name

  
if object_id('tempdb..#t') IS NOT NULL DROP TABLE #t;

  Create table #t(mm VARCHAR(100) )

  INSERT INTO #t( mm) VALUES ( 'ABC' );
INSERT INTO #t( mm) VALUES ( 'DDD' );
INSERT INTO #t( mm) VALUES ( 'XXX' );
INSERT INTO #t( mm) VALUES ( 'YYY' );

  Select * FROM #t;

  --Instead of 4 rows I would like one line and the
--output should look like
--ABC,DDD,XXX,YYY

  -- I Know there is a some nice way to do this using XML SYNTAX
-- and without using the CONCATENATE FUNCTION
--The following work but gives a wierd column name.
--How can we rename the column name to something readable ?

  SELECT ',' + mm AS [text()]
FROM #t
FOR XML PATH('')

  Try:
  
select stuff((select ',' + mm
     from #t
     for xml path(''),TYPE).value('.','varchar(max)'),1,1,'');


  Always use the text() function for this, prevents the reconstruct of the returned set as XML.
  😎

  
select stuff((select ',' + mm
  from #t
  for xml path(''),TYPE).value('(./text())[1]','varchar(max)'),1,1,'');

  


  Lynn:
  if you don;t mind can you help me with adding that TYPE to the following SQL please ?

  
SELECT ID, abc = STUFF(
  (SELECT ',' + mm
  FROM #t t1
  WHERE t1.id = t2.id
  FOR XML PATH (''))
  , 1, 1, '') from #t t2
group by id;


  Sorry , no need, I just figured out.. So is this the solution that Jeff M was talking about.....
  So by using the TYPE you are safe dealing with data that has XML reserved characters

  if object_id('tempdb..#t') IS NOT NULL DROP TABLE #t;

  Create table #t(mm VARCHAR(100), ID int )

  INSERT INTO #t( mm, ID) VALUES ( 'ABC',1 );
INSERT INTO #t( mm, ID) VALUES ( 'DDD',2 );
INSERT INTO #t( mm, ID) VALUES ( 'XXX',2 );
INSERT INTO #t( mm, ID) VALUES ( 'YYY',1 );

  SELECT ID, abc = STUFF(
  (SELECT ',' + mm
  FROM #t t1
  WHERE t1.id = t2.id
  FOR xml path(''),TYPE).value('(./text())[1]','varchar(max)'),1,1,'') from #t t2
group by id;

  If object_id('tempdb..#t') IS NOT NULL DROP TABLE #t;

  Create table #t(mm VARCHAR(100), ID int )

  INSERT INTO #t( mm, ID) VALUES ( 'AB<C',1 );
INSERT INTO #t( mm, ID) VALUES ( '<!=D>DD',2 );
INSERT INTO #t( mm, ID) VALUES ( 'XXX',2 );
INSERT INTO #t( mm, ID) VALUES ( 'YYY',1 );

  --Handles XML reserved characters well!
SELECT ID, abc = STUFF(
  (SELECT ',' + mm
  FROM #t t1
  WHERE t1.id = t2.id
  FOR xml path(''),TYPE).value('(./text())[1]','varchar(max)'),1,1,'') from #t t2
group by id;

  --Fails
SELECT ID, abc = STUFF(
  (SELECT ',' + mm
  FROM #t t1
  WHERE t1.id = t2.id
  FOR XML PATH (''))
  , 1, 1, '') from #t t2
group by id;

  What are you going on about? I've demonstrated how to do this.
  😎

  You need to understand how this works.

  Thank you for help Lynn, Yep, I understand after experimenting. Yes  😎

  You are welcome, but it wasn't me that helped you with adding ,TYPE to your code.