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.

Query

2 posts in General Discussion Last posting was on 2009-08-06 08:12:43.0Z
Rahul Posted on 2009-08-06 07:31:21.0Z
Sender: 6bfb.4a7a83ff.1804289383@sybase.com
From: Rahul
Newsgroups: ianywhere.public.general
Subject: Query
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a7a86c9.6e1a.1681692777@sybase.com>
NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
Date: 6 Aug 2009 00:31:21 -0700
X-Trace: forums-3-dub.sybase.com 1249543881 10.22.241.188 (6 Aug 2009 00:31:21 -0700)
X-Original-Trace: 6 Aug 2009 00:31:21 -0700, forums-3-dub.sybase.com
Lines: 25
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7678
Article PK: 6072

Hi,
I have one table called employee having empno, ename as
fields where empno is primary key.
The other table is project which has two columns empno and
project_code. One empno can be allocated to mulitple
projects_code.
Project Table:
empno proj_code
e1 p1
e1 p2
e1 p3
e2 p1
e3 p4
e3 p5

I want to write a query to get following output using emp
and project tables to get following output
emp name empno Proj Allocated
ABC e1 p1,p2,p3
XYZ e2 p1
BVQ e3 p4,p5

Please treat this query as urgent and kindly reply me.
Thanks
Rahul


Volker Barth <No_VBarth Posted on 2009-08-06 08:12:43.0Z
From: Volker Barth <No_VBarth@Spam_GLOBAL-FINANZ.de>
User-Agent: Thunderbird 2.0.0.22 (Windows/20090605)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Query
References: <4a7a86c9.6e1a.1681692777@sybase.com>
In-Reply-To: <4a7a86c9.6e1a.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a7a907b$1@forums-3-dub.sybase.com>
Date: 6 Aug 2009 01:12:43 -0700
X-Trace: forums-3-dub.sybase.com 1249546363 10.22.241.152 (6 Aug 2009 01:12:43 -0700)
X-Original-Trace: 6 Aug 2009 01:12:43 -0700, vip152.sybase.com
Lines: 42
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7679
Article PK: 6068

Something like (untested!):

select ename as "emp name", e.empno, list(projcode order by projcode) as
"Proj allocated"
from employee e inner join project p on e.empno = p.empno
group by ename, e.empno
order by ename

Note: The list() aggregate with order by is poosible with SQL Anywhere
9ff, IIRC. If you use an older version, just leave out the " order by
projcode".

HTH
Volker


Rahul schrieb:

> Hi,
> I have one table called employee having empno, ename as
> fields where empno is primary key.
> The other table is project which has two columns empno and
> project_code. One empno can be allocated to mulitple
> projects_code.
> Project Table:
> empno proj_code
> e1 p1
> e1 p2
> e1 p3
> e2 p1
> e3 p4
> e3 p5
>
> I want to write a query to get following output using emp
> and project tables to get following output
> emp name empno Proj Allocated
> ABC e1 p1,p2,p3
> XYZ e2 p1
> BVQ e3 p4,p5
>
> Please treat this query as urgent and kindly reply me.
> Thanks
> Rahul