COncaenating rows for the same value for a variabl

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

COncaenating rows for the same value for a variabl

Author

Topic

jayram11
Yak Posting Veteran

97 Posts

Posted - 2011-03-18 : 09:50:27

Hello
i need to concatenate the string in a row for DESC with another row for the same group by variable Value
this is my table

Value SEQ N DESC SHORT DESC
A4422 00100 3 OSTOMY ABSORBENT OSTOMY
A4422 00200 4 THICKEN LIQUID
A4423 00100 3 OSTOMY POUCH, OSTOMY
A4423 00200 4 PIECE), EACH
A4424 00100 3 OSTOMY POUCH, OSTOMY
A4425 00100 3 OSTOMY POUCH, OSTOMY
A4425 00200 4 FILTER (2 PIECE SYSTEM)

This is what i want

Value SEQ N DESC SHORT DES
A4422 00100 3 OSTOMY ABSORBENT THICKEN LIQUID OSTOMY
A4423 00100 3 OSTOMY POUCH, PIECE), EACH OSTOMY
A4424 00100 3 OSTOMY POUCH, DRAINABLE, WITH BARRIEROSTOMY
A4425 00100 3 OSTOMY POUCH, FILTER (2 PIECE SYSTEM)

i need to retrieve just Value seq desc and Short desc column in my table

Any help

THanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-18 : 10:06:07

Could you make this
A4422 00100 3 OSTOMY ABSORBENT OSTOMY
A4422 00200 4 THICKEN LIQUID
A4423 00100 3 OSTOMY POUCH, OSTOMY
A4423 00200 4 PIECE), EACH
A4424 00100 3 OSTOMY POUCH, OSTOMY
A4425 00100 3 OSTOMY POUCH, OSTOMY
A4425 00200 4 FILTER (2 PIECE SYSTEM)

into usable sample data? It isn't obvious which value is the DESC and which is the Short Desc
For Example
SELECT 'A4422','00100', 3, 'OSTOMY','ABSORBENT OSTOMY' UNION ALL
SELECT etc.

Jim

Everyday I learn something that somebody else already knew

jayram11
Yak Posting Veteran

97 Posts

Posted - 2011-03-18 : 11:13:42

I am sorry, My bad . Thanks. The seq num increases from 00100 to 00200 if there are two line of DESC entry for that Value in the next record and so on to 00300 for 3 entries, 00400 etc.

Value SEQ N DESC SHORT DESC
A4422 00100 3 OSTOMY ABSORBENT OSTOMY
A4422 00200 4 THICKEN LIQUID
A4423 00100 3 OSTOMY POUCH, OSTOMY
A4423 00200 4 PIECE), EACH
A4424 00100 3 OSTOMY POUCH, OSTOMY
A4425 00100 3 OSTOMY POUCH, OSTOMY
A4425 00200 4 FILTER (2 PIECE SYSTEM)

This is what i want

Value SEQ N DESC SHORT DES
A4422 00100 3 OSTOMY ABSORBENT THICKEN LIQUID OSTOMY
A4423 00100 3 OSTOMY POUCH, PIECE), EACH OSTOMY
A4424 00100 3 OSTOMY POUCH, DRAINABLE, WITH BARRIER OSTOMY
A4425 00100 3 OSTOMY POUCH, FILTER (2 PIECE SYSTEM) OSTOMY

jayram11
Yak Posting Veteran

97 Posts

Posted - 2011-03-18 : 11:14:17

Crap
for some reson it is not formatiing the way i like. gimme a min

jayram11
Yak Posting Veteran

97 Posts

Posted - 2011-03-18 : 11:17:19

I am sorry, My bad . Thanks. The seq num increases from 00100 to 00200 if there are two line of DESC entry for that Value in the next record and so on to 00300 for 3 entries, 00400 etc. i am using --- for spaces

Value SEQ --N DESC-----------------------------------SHORT DESC
A4422 00100 3 OSTOMY ABSORBENT ----------------------OSTOMY
A4422 00200 4 THICKEN LIQUID
A4423 00100 3 OSTOMY POUCH, -------------------------OSTOMY
A4423 00200 4 PIECE), EACH
A4424 00100 3 OSTOMY POUCH, -------------------------OSTOMY
A4425 00100 3 OSTOMY POUCH, -------------------------OSTOMY
A4425 00200 4 FILTER (2 PIECE SYSTEM)

This is what i want

Value SEQ N DESC--------------------------------------------SHORT DES
A4422 00100 3 OSTOMY ABSORBENT THICKEN LIQUID-----------------OSTOMY
A4423 00100 3 OSTOMY POUCH, PIECE), EACH----------------------OSTOMY
A4424 00100 3 OSTOMY POUCH, DRAINABLE, WITH BARRIER-----------OSTOMY
A4425 00100 3 OSTOMY POUCH, FILTER (2 PIECE SYSTEM)-----------OSTOMY

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-18 : 11:20:28

My suggestion would have been better

SELECT 'A4422','00100', 3, 'OSTOMY','ABSORBENT OSTOMY' UNION ALL
SELECT etc.

Unless you're trying to make me do more so you can do less!

Jim

Read this

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Everyday I learn something that somebody else already knew

jayram11
Yak Posting Veteran

97 Posts

Posted - 2011-03-18 : 11:35:46

Jim, that is the table structure i have. Your suggestion never came to my mind. But i hope you understand what i am trying to get?

jayram11
Yak Posting Veteran

97 Posts

Posted - 2011-03-18 : 15:30:56

I used this function to do this

CREATE FUNCTION recursive ( @cid VARCHAR(5), @i INT )

RETURNS VARCHAR(8000) AS BEGIN

DECLARE @r VARCHAR(8000), @l VARCHAR(8000)

SELECT @i = @i - 1, @r = DESC + ' '

FROM TABLE p1

WHERE VALUE = @cid

AND @i = ( SELECT COUNT(*) FROM TABLE p2

WHERE p2.VALUE = p1.VALUE

AND p2.SEQ <= p1.SEQ) ;

IF @i > 0 BEGIN

EXEC @l = dbo.recursive @cid, @i ;

SET @r = @l + @r ;

END

RETURN @r ;

END

SELECT VALUE,
dbo.recursive( VALUE, COUNT(DESC) ) as NEW_DESC
FROM TABLE
GROUP BY VALUE;

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-18 : 23:44:30

looks to me you need some string concatenation. see this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources