Sybase Database - Another SQL question

Author

Another SQL question

Cal

2006-03-16, 7:23 am

Hi, all.

Situation:
------------------------------------------------------------------

create table source(ppl_id integer, code char(2));
insert into source (ppl_id, code) values (1,'10');
insert into source (ppl_id, code) values (1,'20');

create table target (ppl_id integer, codes(20));
------------------------------------------------------------------
So, in source table I have two rows:
1, '10'
1, '20'

In target I need one row: 1, '10, 20'.

I have to collect all codes for given ppl_id and form a string with ",
" as delimiter.
Delimiter part is easy, but how to select data in needed way?

ZeldorBlat

2006-03-16, 8:23 pm

Cal wrote:
> Hi, all.
>
> Situation:
> ------------------------------------------------------------------
>
> create table source(ppl_id integer, code char(2));
> insert into source (ppl_id, code) values (1,'10');
> insert into source (ppl_id, code) values (1,'20');
>
> create table target (ppl_id integer, codes(20));
> ------------------------------------------------------------------
> So, in source table I have two rows:
> 1, '10'
> 1, '20'
>
> In target I need one row: 1, '10, 20'.
>
> I have to collect all codes for given ppl_id and form a string with ",
> " as delimiter.
> Delimiter part is easy, but how to select data in needed way?

With something other than just SQL? What you're really trying to do is
a sort of "cross-tab" which SQL, by itself, is not very good at.

Galen Boyer

2006-03-17, 3:23 am

On 16 Mar 2006, kasis_100@yahoo.com wrote:
> Hi, all.
>
> Situation:
> ------------------------------------------------------------------
>
> create table source(ppl_id integer, code char(2));
> insert into source (ppl_id, code) values (1,'10');
> insert into source (ppl_id, code) values (1,'20');
>
> create table target (ppl_id integer, codes(20));
> ------------------------------------------------------------------
> So, in source table I have two rows:
> 1, '10'
> 1, '20'
>
> In target I need one row: 1, '10, 20'.
>
> I have to collect all codes for given ppl_id and form a string with ",
> " as delimiter.
> Delimiter part is easy, but how to select data in needed way?

Why not write a proc?

--
Galen Boyer

--CELKO--

2006-03-17, 11:23 am

Look up "Firdt Noremal Form" (1NF) and learn it.

duchuy

2006-03-17, 11:23 am

Hi, i hv no idea on delimiter, but the SQL code for tuple as 1, 10, 20.

Select s1.ppl_id, s1.code, s2.code
>From source s1, source s2
Where s1.ppl_id = s2.ppl_id
And s1.code <> s2.code
( And s1.code < s2.code) (This line depends on how u want the tuple
displaced, or else, we have 1, 10, 20 and 1, 20, 10)

Hope it help (in some mean)

lakshmi

2006-03-21, 3:23 am

I think this should solve - I got it from a FAQ
HTH

6.2.11: Is it possible to concatenate all the values from a column and
return a
single row?

-------------------------------------------------------------------------------

Hey, this was quite cool I thought. It is now possible to concatenate a
series
of strings to return a single column, in a sort of analogous manner to
sum
summing all of the numbers in a column. Obviously, in versions before
12.5,
the longest string that you can have is 255 characters, but with very
long
varchars, this may prove useful to someone.

Use a case statement, a la,

1> declare @string_var varchar(255)
2>
3> select @string_var = ""
4>
5> select @string_var = @string_var +
6> (case 1 when 1
7> then char_col
8> end)
9> from tbl_a
10>
11> print "%1!", @string_var
12> go
(1 row affected)
ABCDEFGH
(8 rows affected)
1> select * from tbl_a
2> go
char_col
--------
A
B
C
D
E
F
G
H

(8 rows affected)
1>
BOL
Lakshmi

Cal wrote:
> Hi, all.
>
> Situation:
> ------------------------------------------------------------------
>
> create table source(ppl_id integer, code char(2));
> insert into source (ppl_id, code) values (1,'10');
> insert into source (ppl_id, code) values (1,'20');
>
> create table target (ppl_id integer, codes(20));
> ------------------------------------------------------------------
> So, in source table I have two rows:
> 1, '10'
> 1, '20'
>
> In target I need one row: 1, '10, 20'.
>
> I have to collect all codes for given ppl_id and form a string with ",
> " as delimiter.
> Delimiter part is easy, but how to select data in needed way?

lakshmi

2006-03-21, 3:23 am

Cal

2006-03-30, 7:23 am

Hi.

Well, it only returns error "Select returns more than 1 row".

Cal

2006-03-30, 7:23 am

Hi.

How that would look like? Make cursor, go through all rows, put
together string from fields?

Cal

2006-03-30, 7:23 am

Hi.

This is data warehouse environment, normal forms do not apply here.

Sponsored Links