one Rows in two Line in Result one line in one row

• tutter

  SSC Enthusiast

  Points: 116

  More actions

  May 29, 2020 at 5:44 am

  #3758356

  Hello,

  i have often the Problem that i must the information from 2 line (1 row) i need in 1 line

  Example:

  Select car,model,engine,details from cars

  ( |= stands for row)

  Honda| Civic| 1.6| deluxe

  Honda| Civic| 1.6| basic

  VW| Golf| 1.5| basic

  VW| Golf| 1.5| extra

  Dodge| Viper| 8.4| performance

  Dodge| Viper| 8.4| last_edition

   

  Result: Row 4 but only 1 line

  Honda| Civic| 1.6| deluxe, basic

  VW| Golf| 1.5| basic, extra

  Dodge| Viper| 8.4| performance, last_edition

  Thanks for helping

• Ken McKelvey

  SSCoach

  Points: 18296

  More actions

  May 29, 2020 at 8:58 am

  #3758389

  Using FOR XML PATH with .value is the normal approach for this. See just before 'Using Common Language Runtime' here:

  https://www.red-gate.com/simple-talk/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

• Phil Parkin

  SSC Guru

  Points: 244578

  More actions

  May 29, 2020 at 12:00 pm

  #3758524

  If you would like help creating a solution in code, please provide your data in consumable format (ie, one which can be cut & pasted into SSMS).

  If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
  See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

• Jeff Moden

  SSC Guru

  Points: 996623

  More actions

  May 29, 2020 at 5:56 pm

  #3758748

  Since you're new, let me explain what Phil means by directing you to the first link in my signature line below.  It helps others help you both a lot better and a lot more quickly.

  Here's another way to provide such readily consumable data (I'm using what you posted as the example).

  --===== If the test table already exists, drop it to make reruns in SSMS easier.
       -- This is NOT a part of the solution to the provlem. This is how to post test data for the problem.
     DROP TABLE IF EXISTS #TestTable
  ;
  --===== Create and populate the test table on-the-fly.
   SELECT *
     INTO #TestTable
     FROM (VALUES
           ('Honda','Civic','1.6','deluxe')
          ,('Honda','Civic','1.6','basic')
          ,('VW','Golf','1.5','basic')
          ,('VW','Golf','1.5','extra')
          ,('Dodge','Viper','8.4','performance')
          ,('Dodge','Viper','8.4','last_edition')
          )v(car,model,engine,details)
  ;
  GO

  Once that's done and because you're using SQL Server 2017, we no longer need the XML trick to do this.  Instead, we can use the new STRING_AGG() function as follows:

   SELECT  car,model,engine
          ,details = STRING_AGG(details,', ' ) WITHIN GROUP (ORDER BY details)
     FROM #TestTable
    GROUP BY car,model,engine
    ORDER BY car,model,engine
  ;

  ... and that produces the following results...

  

  --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".
  "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"
  

  ---

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