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

Question

Post reply

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18843 More actions · Answer 1

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: 246965 More actions · Answer 2

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

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.

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