Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need to merge multiple rows into one with carriage return Expand / Collapse
Author
Message
Posted Monday, November 8, 2010 2:05 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 13, 2014 6:57 PM
Points: 148, Visits: 640
DECLARE @Sample TABLE
(
ID INT
, Description varchar(100)
)

INSERT @Sample
SELECT 1234, 'I' UNION ALL
SELECT 1234, 'love' UNION ALL
SELECT 1234, 'sql server' UNION ALL
SELECT 3234, 'i' UNION ALL
SELECT 3234, 'despise' UNION ALL
SELECT 3234, '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
Post #1017453
Posted Monday, November 8, 2010 2:13 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1017464
Posted Monday, November 8, 2010 2:15 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 12:57 PM
Points: 576, Visits: 692
Use CHAR(13)

so something like this..

DECLARE @str varchar(50)

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

PRINT @str

Post #1017468
Posted Monday, November 8, 2010 2:22 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 13, 2014 6:57 PM
Points: 148, Visits: 640
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?
Post #1017477
Posted Monday, November 8, 2010 2:26 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1017480
Posted Tuesday, November 9, 2010 2:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 11:43 PM
Points: 3, Visits: 97
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

Post #1017699
Posted Tuesday, November 9, 2010 6:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 13, 2014 6:57 PM
Points: 148, Visits: 640
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
Post #1017860
Posted Wednesday, November 10, 2010 4:19 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, May 10, 2011 2:08 PM
Points: 405, Visits: 2,670
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)


Post #1018476
Posted Wednesday, November 10, 2010 6:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 13, 2014 6:57 PM
Points: 148, Visits: 640
Wow! That is awesome, Thank you very much. Works perfectly.
Post #1018540
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse