Concatenate multiple rows into a single row - Very Urgent

Can someone please help me out in writing the Sql to concatenate the
Text_desc for each code for all the seq_nos in the ascending order of seq_no
and load into the target table.

Source table : S1

Code Seq_no Text_desc

1579 0 BCN5, C, ER75
1579 1 FP5, WC6, MATW, PRH
1579 2 T
1600 0 TW, FP5, AS5, MHSA15, DME20
1600 1 ER100, UR40

Target table: T1

Text_desc (concatenate all the strings for each code in ascending order of
seq_no)

code Text_desc

1579 BCN5, C, ER75FP5, WC6, MATW, PRHT
1600 TW, FP5, AS5, MHSA15, DME20ER100, UR40


Thanks,
Maddy

Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This is a more basic programming principle than just
SQL and RDBMS.

Get a reporting tool so you can do a sort for your lists and lay them
out on paper or a screen in a way that a human being can read them.
It would also help if you knew what 1NF means, too.

Ivory towers are great - but some of us work in the real world and have to
produce systems that do stuff.

I agree its not very good practice to denormalise - but when I was at school
the text book said that atoms couldn't be split!

I suggest you try to offer more constructive advice - or don't bother.


wrote:

Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This is a more basic programming principle than just
SQL and RDBMS.
>
Get a reporting tool so you can do a sort for your lists and lay them
out on paper or a screen in a way that a human being can read them.
It would also help if you knew what 1NF means, too.

maddyforums wrote:

This looks a good one for cursors (there are some uses for them) or use
tsql loop - set up a select sorted on Code,Seq_no and loop until the Code
changes.


Can someone please help me out in writing the Sql to concatenate the
Text_desc for each code for all the seq_nos in the ascending order of
seq_no and load into the target table.
>
Source table : S1
>
Code Seq_no Text_desc

1579 0 BCN5, C, ER75
1579 1 FP5, WC6, MATW, PRH
1579 2 T
1600 0 TW, FP5, AS5, MHSA15, DME20
1600 1 ER100, UR40
>
Target table: T1
>
Text_desc (concatenate all the strings for each code in ascending order
of seq_no)
>
code Text_desc

1579 BCN5, C, ER75FP5, WC6, MATW, PRHT
1600 TW, FP5, AS5, MHSA15, DME20ER100, UR40
>
>
Thanks,
Maddy

In article <84eea91231c20@uwe>, u43936@uwe says
Can someone please help me out in writing the Sql to concatenate the
Text_desc for each code for all the seq_nos in the ascending order of seq_no
and load into the target table.

Look in your docs to see if you have the List() function available in
whatever version you are using. In ASA 9 and 10, it will do exactly
what you want here.



--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).

>This looks a good one for cursors (there are some uses for them) or use
tsql loop - set up a select sorted on Code,Seq_no and loop until the
Code
changes. <<

I can write convoluted, poor SQL with the best of them :) I just
choose not to do bad coding. If you were a better SQL programmer, you
would know how to do this without a cursor. First, let's get some DDL
and bring the names closer to IS rules.

CREATE TABLE Foobar
(product_code CHAR(4) NT NULL,
vague_seq INTEGER NT NULL
CHECK (vague_seq >= 0),
PRIMARY KEY (product_code, vague_seq),
product_descr VARCHAR(200) NT NULL);

Here is the answer is a single, Standard SQL statement without
cursors.

SELECT X.product_code,
REPLACE
(MAX(CASE WHEN vague_seq = 0 THEN product_descr || ',' ELSE ''
END)
|| MAX(CASE WHEN vague_seq = 1 THEN product_descr || ',' ELSE '' END)
|| MAX(CASE WHEN vague_seq = 2 THEN product_descr || ',' ELSE '' END)
|| MAX(CASE WHEN vague_seq = 3 THEN product_descr || ',' ELSE '' END)
|| MAX(CASE WHEN vague_seq = 4 THEN product_descr || ',' ELSE '' END)
|| MAX(CASE WHEN vague_seq = 5 THEN product_descr ELSE '' END)
|| '?', ',?', '')
FRM Fooobar AS X
GRUP BY X.product_code;

The REPLACE() is to trim off the last comma. If the sequences are
longer, you can either add more MAX(CASE) or re-number this table
and run it again. either way, it should be about an order of
magnitude faster than a cursor.

Perhaps you should read a text book before you post again :)

And when vague_seq = 6 or 7 or 99 or 999 ????

Sorry, after writing very effective Sybase tsql for 18 years, I still think
cursors or a TSQL loop will work well - and speedily with a cluster index
on code,seq_no



wrote:

This looks a good one for cursors (there are some uses for them) or use
tsql loop - set up a select sorted on Code,Seq_no and loop until the
Code
changes. <<
>
I can write convoluted, poor SQL with the best of them :) I just
choose not to do bad coding. If you were a better SQL programmer, you
would know how to do this without a cursor. First, let's get some DDL
and bring the names closer to IS rules.
>
CREATE TABLE Foobar
(product_code CHAR(4) NT NULL,
vague_seq INTEGER NT NULL
CHECK (vague_seq >= 0),
PRIMARY KEY (product_code, vague_seq),
product_descr VARCHAR(200) NT NULL);
>
Here is the answer is a single, Standard SQL statement without
cursors.
>
SELECT X.product_code,
REPLACE
(MAX(CASE WHEN vague_seq = 0 THEN product_descr || ',' ELSE ''
END)
|| MAX(CASE WHEN vague_seq = 1 THEN product_descr || ',' ELSE '' END)
|| MAX(CASE WHEN vague_seq = 2 THEN product_descr || ',' ELSE '' END)
|| MAX(CASE WHEN vague_seq = 3 THEN product_descr || ',' ELSE '' END)
|| MAX(CASE WHEN vague_seq = 4 THEN product_descr || ',' ELSE '' END)
|| MAX(CASE WHEN vague_seq = 5 THEN product_descr ELSE '' END)
|| '?', ',?', '')
FRM Fooobar AS X
GRUP BY X.product_code;
>
The REPLACE() is to trim off the last comma. If the sequences are
longer, you can either add more MAX(CASE) or re-number this table
and run it again. either way, it should be about an order of
magnitude faster than a cursor.
>
Perhaps you should read a text book before you post again :)