May 29, 2020 at 5:44 am
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
May 29, 2020 at 8:58 am
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/
May 29, 2020 at 12:00 pm
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).
May 29, 2020 at 5:56 pm
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
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy