concatenate multiple rows into a single row

Todas las respuestas

• lunes, 08 de noviembre de 2010 22:15

   

   
  

  0

  Inicie sesión para votar

  I have a table with one column Output.it has values as below.But i need them in one row.How to write a query for this

  Output

  'testing the scenario.exe' is not recognized as an internal or external command,

  operable program or batch file.

  NULL

  the output should be

  output

  testing the scenario.exe' is not recognized as an internal or external command, operable program or batch file. NULL

  thanks

  • Responder
  • Citar

   
• lunes, 08 de noviembre de 2010 22:39

   

   
  

  0

  Inicie sesión para votar

  I have a table with one column Output .it has values as below.But i need them in one row.How to write a query for this

   

  Output

  'testing the scenario.exe' is not recognized as an internal or external command,

  operable program or batch file.

  NULL

  the output should be

   

  output

  testing the scenario.exe' is not recognized as an internal or external command, operable program or batch file. NULL

  thanks

  
  All right, then use COALESCE , just like:

   

  DECLARE @Outputs VARCHAR(8000) 
  SELECT @Outputs = COALESCE(@Outputs + ' ', '') + Output FROM test1
  Select @Outputs
  
  

  
  Hope this helps.

   

  ---

  Willy Taveras.-
  
  http://itlearn.net

  • Propuesto como respuesta Willy Taveras lunes, 08 de noviembre de 2010 22:48
  • Marcado como respuesta Leo00 jueves, 11 de noviembre de 2010 21:40
  •  
  • Responder
  • Citar

   
• martes, 09 de noviembre de 2010 15:08

  Moderador

   

   
  

  0

  Inicie sesión para votar

  Willy,

  There is no guarantee that the order of the concatenation will be as expected, unless you use "order by" clause.

  Said that, there are issues with this approach when using "order by" clause.

  Multi-Row Variable Assignment and ORDER BY

  PRB: Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location 

  ---

  AMB
  

  Some guidelines for posting questions...

  • Propuesto como respuesta Naomi NMicrosoft Community Contributor, Moderator martes, 09 de noviembre de 2010 15:09
  •  
  • Responder
  • Citar

   
• jueves, 11 de noviembre de 2010 21:39

   

   
  

  0

  Inicie sesión para votar
  That worked...Thanks!!
  • Responder
  • Citar

   
• viernes, 12 de noviembre de 2010 0:14

  Moderador

   

   
  

  0

  Inicie sesión para votar

  It could break break in the next run. Do not rely in that kind of code to concatenate values from multiple rows, you better stick to the approach using FOR XML PATH.

  USE tempdb;
  GO
  DECLARE @t TABLE (
  C1 int NOT NULL IDENTITY(1, 1),
  C2 varchar(MAX)
  );
  
  INSERT INTO @T(c2)
  VALUES
    ('testing the scenario.exe is not recognized as an internal or external command,'),
    ('operable program or batch file.'),
    (NULL);
  
  SELECT
  	*
  FROM
  	@T
  ORDER BY
    c1;
  
  SELECT
    (
    SELECT c2 AS [data()]
    FROM @t
    ORDER BY c1
    FOR XML PATH(''), TYPE
    ).value('.', 'varchar(max)');
  GO

  Notice that in order to concatenate the values in the correct order, I used "order by" clause. Without it, there is no guarantee that you will get the expected result.

   

  ---

  AMB
  

  Some guidelines for posting questions...

  • Responder
  • Citar