String manipulation

Question

String manipulation

SD-691870

SSC Enthusiast

Points: 144
More actions
May 13, 2008 at 2:59 pm

#91823

I am trying to combining multiple rows into one for display purpose only.
Would appreciate any help. Thanx 🙂
-------------------------------------------------------------
Create table #test
(PlaceCode int,
Contract nvarchar(20),
SetNo nvarchar(20),
Failure1 nvarchar(50),
Failure2 nvarchar(50),
Failure3 nvarchar(50)
)
Insert into #test
select 13234,'450a','20a','Specs Available',null,'low voltage'
union all
select 13234,'450a','21a','Specs Available',null,null
union all
select 12003,'100c','15a','Specs Available','Exceeds',null
--select * from #test
--Desired output:
PlaceCode Contract SetNo
13234 450a 20A:Specs Available,low voltage
21a :Specs Available
12003 100c 15a: Specs Available,Exceeds

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

This article, http://www.sqlservercentral.com/articles/Advanced+Querying/61716/, has an outline of how to do that kind of thing. Just substitute string concatenation for sub-totals.

- 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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

That's if you need a solution for both SQL Server 2000 and 2005... if it's just for 2005, it gets real easy...

SELECT CASE

WHEN ROW_NUMBER() OVER (PARTITION BY PlaceCode,Contract ORDER BY PlaceCode,Contract) = 1

THEN CAST(PlaceCode AS VARCHAR(10))

ELSE ''

END AS PlaceCode,

Contract,

SetNo+':'+Failure1+ISNULL(','+Failure2,'')+ISNULL(','+Failure3,'') AS SetNo

FROM #Test

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

SD-691870 SSC Enthusiast Points: 144 More actions · Answer 3

Thank you GSquared and Jeff!

I guess people like you'll really inspire the rest of us to do better.

Thanks once again.:)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

Thanks, Sapna... :blush: Such a nice compliment is a great way to start the day. 🙂

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