Need to merge multiple rows into one with carriage return

Question

Need to merge multiple rows into one with carriage return

tan110

Hall of Fame

Points: 3855
More actions
November 8, 2010 at 2:05 pm

#231099

DECLARE@Sample TABLE
(
ID INT
, Description varchar(100)
)
INSERT@Sample
SELECT1234, 'I' UNION ALL
SELECT1234, 'love' UNION ALL
SELECT1234, 'sql server' UNION ALL
SELECT3234, 'i' UNION ALL
SELECT3234, 'despise' UNION ALL
SELECT3234, 'oracle'
Select * from @Sample
I want to return 2 rows, and the Description to have carriage returns between each. Like this:
1234 I
love
sql server
3234 i
despise
oracle

Viewing 9 posts - 1 through 8 (of 8 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

String concatenation, and adding some hard returns into it, should do that.

But why would you want to do presentation-level tasks like that in the database? Do that kind of thing in the web page/app.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Derrick Smith SSChampion Points: 10833 More actions · Answer 2

Use CHAR(13)

so something like this..

DECLARE @STR varchar(50)

SELECT @STR = 'I'+CHAR(13)+'LOVE'+CHAR(13)+'SQL SERVER'

PRINT @STR

tan110 Hall of Fame Points: 3855 More actions · Answer 3

Unfortunately, my boss wants me to do this at the database level. The problem is, I won't know how many rows I'll get. So how do i concatenate row description based on N number of rows?

GSquared SSC Guru Points: 260824 More actions · Answer 4

Search online for "XML path string concatenation". It's a pretty slick trick.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

_DJ Valued Member Points: 67 More actions · Answer 5

try like this

SELECT

ID,

[Description] = MAX(o.list)

FROM

@Sample T1

CROSS APPLY

(

SELECT ISNULL([Description], '') AS [data()]

FROM @Sample T2

WHERE T2.ID = T1.ID

FOR XML PATH ('')

)o(list)

GROUP BY ID

tan110 Hall of Fame Points: 3855 More actions · Answer 6

Getting closer:

Using

SELECT

ID,

[Description] = MAX(o.list)

FROM

@Sample T1

CROSS APPLY

(

SELECT ISNULL([Description], '' ) AS [data()]

FROM @Sample T2

WHERE T2.ID = T1.ID

FOR XML PATH ('')

)o(list)

GROUP BY ID

My results come out: (Using results to text)

1234 I love sql server

3234 i despise oracle

How do I add a carriage return, so my output looks like this:

1234 I

love

sql server

3234 i

despise

oracle

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 7

try this

;WITH cte AS

(

SELECT DISTINCT ID

FROM @Sample

)

SELECT cte.ID, Z.Descriptions

FROM cte

CROSS APPLY

(

SELECT (STUFF((SELECT CHAR(13) + Description

FROM @Sample AS Sample

WHERE Sample.ID = cte.ID

FOR XML PATH(''), TYPE).value('.[1]', 'varchar(MAX)'), 1, 1, ''))

) AS Z (Descriptions)

tan110 Hall of Fame Points: 3855 More actions · Answer 8

Wow! That is awesome, Thank you very much. Works perfectly.