Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

concatenate a column from multiple rows

7 posts in General Discussion Last posting was on 2010-08-17 06:24:03.0Z
Raj Posted on 2010-08-09 02:18:19.0Z
Sender: 3f76.4c5baf73.1804289383@sybase.com
From: Raj
Newsgroups: sybase.public.ase.general
Subject: concatenate a column from multiple rows
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4c5f656b.2e5f.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 8 Aug 2010 19:18:19 -0700
X-Trace: forums-1-dub 1281320299 10.22.241.41 (8 Aug 2010 19:18:19 -0700)
X-Original-Trace: 8 Aug 2010 19:18:19 -0700, 10.22.241.41
Lines: 27
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29460
Article PK: 78691

Hello,

I am not sure if its right to post it here, I would like
to know the SQL to concatenate a column from multiple rows
in an object?? I am using TransactSQL.

I have an object with below records

id name
--- ----
1 raj
2 abc
3 xyz
1 red
2 green
3 pink

I need the ourput like below

id name
--- ----
1 raj,red
2 abc,green
3 xyz,pink

Thanks in advance
Raj


Carl Kayser Posted on 2010-08-10 11:43:34.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Newsgroups: sybase.public.ase.general
References: <4c5f656b.2e5f.1681692777@sybase.com>
Subject: Re: concatenate a column from multiple rows
Lines: 96
Organization: BLS
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5931
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c613b66$1@forums-1-dub>
Date: 10 Aug 2010 04:43:34 -0700
X-Trace: forums-1-dub 1281440614 10.22.241.152 (10 Aug 2010 04:43:34 -0700)
X-Original-Trace: 10 Aug 2010 04:43:34 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29461
Article PK: 78692


<Raj> wrote in message news:4c5f656b.2e5f.1681692777@sybase.com...
> Hello,
>
> I am not sure if its right to post it here, I would like
> to know the SQL to concatenate a column from multiple rows
> in an object?? I am using TransactSQL.
>
> I have an object with below records
>
> id name
> --- ----
> 1 raj
> 2 abc
> 3 xyz
> 1 red
> 2 green
> 3 pink
>
> I need the ourput like below
>
> id name
> --- ----
> 1 raj,red
> 2 abc,green
> 3 xyz,pink
>
> Thanks in advance
> Raj

Your table appears to be very denormalised. (Bad design?) The following
does the trick but could be very slow with a large table. And I wouldn't
want to generalize to more than two columns being concatenated.


create table #test
(id tinyint not null,
name varchar (10) not null)

insert into #test values (1, 'raj')
insert into #test values (2, 'abc')
insert into #test values (3, 'xyz')
insert into #test values (1, 'red')
insert into #test values (2, 'green')
insert into #test values (3, 'pink')

create table #test2
(id tinyint not null,
name varchar (10) not null,
seq tinyint identity)

insert into #test2
select *
from #test

select *
from #test2

select t1.name || ', ' || t2.name
from #test2 t1,
#test2 t2
where t1.id = t2.id
and t1.seq = (select min (t3.seq)
from #test2 t3
where t3.id = t1.id)
and t2.seq = (select max (t4.seq)
from #test2 t4
where t4.id = t1.id)
group by t1.id
having t1.id = t2.id
and t1.seq = (select min (t3.seq)
from #test2 t3
where t3.id = t1.id)
and t2.seq = (select max (t4.seq)
from #test2 t4
where t4.id = t1.id)
order by t1.seq

id name seq
--- ---------- ---
1 raj 1
2 abc 2
3 xyz 3
1 red 4
2 green 5
3 pink 6

----------------------
raj, red
abc, green
xyz, pink


Raj.. Posted on 2010-08-17 06:24:03.0Z
Sender: 168c.4c6a2767.1804289383@sybase.com
From: Raj..
Newsgroups: sybase.public.ase.general
Subject: Re: concatenate a column from multiple rows
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4c6a2b03.16e9.1681692777@sybase.com>
References: <4c613b66$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 16 Aug 2010 23:24:03 -0700
X-Trace: forums-1-dub 1282026243 10.22.241.41 (16 Aug 2010 23:24:03 -0700)
X-Original-Trace: 16 Aug 2010 23:24:03 -0700, 10.22.241.41
Lines: 99
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29468
Article PK: 78699


> <Raj> wrote in message
> > news:4c5f656b.2e5f.1681692777@sybase.com... Hello,
> >
> > I am not sure if its right to post it here, I would
> > like to know the SQL to concatenate a column from
> > multiple rows in an object?? I am using TransactSQL.
> >
> > I have an object with below records
> >
> > id name
> > --- ----
> > 1 raj
> > 2 abc
> > 3 xyz
> > 1 red
> > 2 green
> > 3 pink
> >
> > I need the ourput like below
> >
> > id name
> > --- ----
> > 1 raj,red
> > 2 abc,green
> > 3 xyz,pink
> >
> > Thanks in advance
> > Raj
>
>
> Your table appears to be very denormalised. (Bad design?)
> The following does the trick but could be very slow with
> a large table. And I wouldn't want to generalize to more
> than two columns being concatenated.
>
>
> create table #test
> (id tinyint not null,
> name varchar (10) not null)
>
> insert into #test values (1, 'raj')
> insert into #test values (2, 'abc')
> insert into #test values (3, 'xyz')
> insert into #test values (1, 'red')
> insert into #test values (2, 'green')
> insert into #test values (3, 'pink')
>
> create table #test2
> (id tinyint not null,
> name varchar (10) not null,
> seq tinyint identity)
>
> insert into #test2
> select *
> from #test
>
> select *
> from #test2
>
> select t1.name || ', ' || t2.name
> from #test2 t1,
> #test2 t2
> where t1.id = t2.id
> and t1.seq = (select min (t3.seq)
> from #test2 t3
> where t3.id = t1.id)
> and t2.seq = (select max (t4.seq)
> from #test2 t4
> where t4.id = t1.id)
> group by t1.id
> having t1.id = t2.id
> and t1.seq = (select min (t3.seq)
> from #test2 t3
> where t3.id = t1.id)
> and t2.seq = (select max (t4.seq)
> from #test2 t4
> where t4.id = t1.id)
> order by t1.seq
>
> id name seq
> --- ---------- ---
> 1 raj 1
> 2 abc 2
> 3 xyz 3
> 1 red 4
> 2 green 5
> 3 pink 6
>
> ----------------------
> raj, red
> abc, green
> xyz, pink
>
>
>
>

Thanks.


Bret Halford Posted on 2010-08-10 15:21:02.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.11) Gecko/20100711 Thunderbird/3.0.6
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: concatenate a column from multiple rows
References: <4c5f656b.2e5f.1681692777@sybase.com>
In-Reply-To: <4c5f656b.2e5f.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c616e5e$1@forums-1-dub>
Date: 10 Aug 2010 08:21:02 -0700
X-Trace: forums-1-dub 1281453662 10.22.241.152 (10 Aug 2010 08:21:02 -0700)
X-Original-Trace: 10 Aug 2010 08:21:02 -0700, vip152.sybase.com
Lines: 39
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29462
Article PK: 78693


On 8/8/2010 8:18 PM, Raj wrote:
> Hello,
>
> I am not sure if its right to post it here, I would like
> to know the SQL to concatenate a column from multiple rows
> in an object?? I am using TransactSQL.
>
> I have an object with below records
>
> id name
> --- ----
> 1 raj
> 2 abc
> 3 xyz
> 1 red
> 2 green
> 3 pink
>
> I need the ourput like below
>
> id name
> --- ----
> 1 raj,red
> 2 abc,green
> 3 xyz,pink
>
> Thanks in advance
> Raj

Is there any requirement on the order of the concatenated values?
In your example results, the order is alphabetical for ids 1 and 2
but not for 3.

Is there some known maximum number of values for each id?

If there is no reasonably small limit, then using a cursor might
actually be the best solution.


Rajasekhar Reddy Posted on 2010-08-17 06:20:43.0Z
Sender: 168c.4c6a2767.1804289383@sybase.com
From: Rajasekhar Reddy
Newsgroups: sybase.public.ase.general
Subject: Re: concatenate a column from multiple rows
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4c6a2a3b.16d4.1681692777@sybase.com>
References: <4c616e5e$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 16 Aug 2010 23:20:43 -0700
X-Trace: forums-1-dub 1282026043 10.22.241.41 (16 Aug 2010 23:20:43 -0700)
X-Original-Trace: 16 Aug 2010 23:20:43 -0700, 10.22.241.41
Lines: 42
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29467
Article PK: 78698


> On 8/8/2010 8:18 PM, Raj wrote:
> > Hello,
> >
> > I am not sure if its right to post it here, I would
> > like to know the SQL to concatenate a column from
> > multiple rows in an object?? I am using TransactSQL.
> >
> > I have an object with below records
> >
> > id name
> > --- ----
> > 1 raj
> > 2 abc
> > 3 xyz
> > 1 red
> > 2 green
> > 3 pink
> >
> > I need the ourput like below
> >
> > id name
> > --- ----
> > 1 raj,red
> > 2 abc,green
> > 3 xyz,pink
> >
> > Thanks in advance
> > Raj
>
>
> Is there any requirement on the order of the concatenated
> values? In your example results, the order is alphabetical
> for ids 1 and 2 but not for 3.
>
> Is there some known maximum number of values for each id?
>
> If there is no reasonably small limit, then using a cursor
> might actually be the best solution.
>

Order is not really matter. NO, don't know the maximum no.of
values for each id. ok.


"Paul Horan[Sybase]" <phoran_remove Posted on 2010-08-12 19:39:50.0Z
From: "Paul Horan[Sybase]" <phoran_remove@remove_sybase.com>
Newsgroups: sybase.public.ase.general
References: <4c5f656b.2e5f.1681692777@sybase.com>
Subject: Re: concatenate a column from multiple rows
Lines: 41
Organization: Sybase
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5931
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c644e06$1@forums-1-dub>
Date: 12 Aug 2010 12:39:50 -0700
X-Trace: forums-1-dub 1281641990 10.22.241.152 (12 Aug 2010 12:39:50 -0700)
X-Original-Trace: 12 Aug 2010 12:39:50 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29464
Article PK: 78695

Or - get a copy of SQLAnywhere, which has the List() aggregate on strings.

Select id, list( name ) from table
group by id ;

Submit an enhancement request to add this into ASE...

--
Paul Horan[Sybase]
http://paulhoran.ulitzer.com

<Raj> wrote in message news:4c5f656b.2e5f.1681692777@sybase.com...
> Hello,
>
> I am not sure if its right to post it here, I would like
> to know the SQL to concatenate a column from multiple rows
> in an object?? I am using TransactSQL.
>
> I have an object with below records
>
> id name
> --- ----
> 1 raj
> 2 abc
> 3 xyz
> 1 red
> 2 green
> 3 pink
>
> I need the ourput like below
>
> id name
> --- ----
> 1 raj,red
> 2 abc,green
> 3 xyz,pink
>
> Thanks in advance
> Raj


Carl Kayser Posted on 2010-08-14 12:59:35.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Newsgroups: sybase.public.ase.general
References: <4c5f656b.2e5f.1681692777@sybase.com> <4c644e06$1@forums-1-dub>
Subject: Re: concatenate a column from multiple rows
Lines: 50
Organization: BLS
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5931
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c669337$1@forums-1-dub>
Date: 14 Aug 2010 05:59:35 -0700
X-Trace: forums-1-dub 1281790775 10.22.241.152 (14 Aug 2010 05:59:35 -0700)
X-Original-Trace: 14 Aug 2010 05:59:35 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29465
Article PK: 78696


"Paul Horan[Sybase]" <phoran_remove@remove_sybase.com> wrote in message
news:4c644e06$1@forums-1-dub...
> Or - get a copy of SQLAnywhere, which has the List() aggregate on strings.
>
> Select id, list( name ) from table
> group by id ;
>
> Submit an enhancement request to add this into ASE...
>

Already submitted as ISUG ER 2042 a long time ago by ... Paul Horan. It
looks like ASE does not plan to implement it.

> --
> Paul Horan[Sybase]
> http://paulhoran.ulitzer.com
>
> <Raj> wrote in message news:4c5f656b.2e5f.1681692777@sybase.com...
>> Hello,
>>
>> I am not sure if its right to post it here, I would like
>> to know the SQL to concatenate a column from multiple rows
>> in an object?? I am using TransactSQL.
>>
>> I have an object with below records
>>
>> id name
>> --- ----
>> 1 raj
>> 2 abc
>> 3 xyz
>> 1 red
>> 2 green
>> 3 pink
>>
>> I need the ourput like below
>>
>> id name
>> --- ----
>> 1 raj,red
>> 2 abc,green
>> 3 xyz,pink
>>
>> Thanks in advance
>> Raj
>
>