concatenation of multiple rows

• Teemo

  Ten Centuries

  Points: 1376

  More actions

  November 2, 2011 at 11:27 pm

  #251059

  hello community,

  guys I need help on this one, Here is the scenario

  CREATE TABLE #tmpdata (

  nkey int ,msg nvarchar(2000))

  INSERT INTO #tmpdata (nkey, msg)VALUES(100,'The quick brown fox')

  INSERT INTO #tmpdata (nkey, msg)VALUES(100,'Jump over the lazy dog')

  INSERT INTO #tmpdata (nkey, msg)VALUES(200,'Another long message(A)')

  INSERT INTO #tmpdata (nkey, msg)VALUES(300,'Another long message(B)')

  SELECT * FROM #tmpdata

  CREATE TABLE #tmpdataFinalOutput (

  nkey int ,msg nvarchar(2000))

  INSERT INTO #tmpdataFinalOutput (nkey,msg) VALUES (100,'The quick brown fox|Jump over the lazy dog')

  INSERT INTO #tmpdataFinalOutput (nkey,msg) VALUES (200,'Another long message(A)')

  INSERT INTO #tmpdataFinalOutput (nkey,msg) VALUES (300,'Another long message(B)')

  SELECT * FROM #tmpdataFinalOutput

  you will notice that on the final output, the msg data was concatenated using | and the only record that was concatenated are having the same nkey value. Is this possible to achieve? Please help.

  Thanks in advance

  teemo

• Teemo

  Ten Centuries

  Points: 1376

  More actions

  November 3, 2011 at 2:11 am

  #1402514

  guys i think i did it,

  SELECT nkey,

  (SELECT msg + '|'

  FROM #tmpdata r2

  WHERE r2.nkey = r1.nkey

  ORDER BY msg

  FOR XML PATH('')) AS fmsg

  FROM #tmpdata r1

  GROUP BY nkey

  but my problem is that at the end of the string , the delimiter | was included.

  Please do suggest a workaround on removing this.

  thanks in advance

  teemo

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  November 3, 2011 at 2:39 am

  #1402521

  Teemo (11/3/2011)

  ---

  guys i think i did it,

  SELECT nkey,

  (SELECT msg + '|'

  FROM #tmpdata r2

  WHERE r2.nkey = r1.nkey

  ORDER BY msg

  FOR XML PATH('')) AS fmsg

  FROM #tmpdata r1

  GROUP BY nkey

  but my problem is that at the end of the string , the delimiter | was included.

  Please do suggest a workaround on removing this.

  thanks in advance

  teemo

  SELECT nkey

  ,STUFF((

  SELECT '|' + msg

  FROM #tmpdata r2

  WHERE r2.nkey = r1.nkey

  ORDER BY msg

  FOR XML PATH('')

  ), 1, 1, '') AS fmsg

  FROM #tmpdata r1

  GROUP BY nkey

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• Teemo

  Ten Centuries

  Points: 1376

  More actions

  November 3, 2011 at 2:46 am

  #1402525

  great, thanks for the answer, this will be my first time using this method ( STUFF)