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.

crosstab like in infomaker

2 posts in General Discussion Last posting was on 2003-12-20 16:59:30.0Z
Martin Lungershausen Posted on 2003-12-20 12:50:32.0Z
From: Martin Lungershausen <martin@teleworker-center.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de-AT; rv:1.4) Gecko/20030624
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: crosstab like in infomaker
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: p5088f7f3.dip.t-dialin.net
X-Original-NNTP-Posting-Host: p5088f7f3.dip.t-dialin.net
Message-ID: <3fe44598@forums-1-dub>
Date: 20 Dec 2003 04:50:32 -0800
X-Trace: forums-1-dub 1071924632 80.136.247.243 (20 Dec 2003 04:50:32 -0800)
X-Original-Trace: 20 Dec 2003 04:50:32 -0800, p5088f7f3.dip.t-dialin.net
Lines: 32
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2216
Article PK: 4423

Can anyone help me making a crosstab like in inofmaker with ianywhere
8.x or 9.x

I have different categories on each row

like

trip_number order_number pers_number description amount
999 1 1 doubleroom 1
999 1 1 insurance 1
999 1 1 singleroom 0
999 2 2 doubleroom 0
999 2 2 insurance 0
999 2 2 singleroom 1

and want to get in a report

trip_number order_number pers_numer doubleroom insurance singleroom
999 1 1 1 1 0
999 2 2 0 0 1

the amount of rows can change by each trip so in the report for a trip
there can be different coloums (not in one report)

and then I want to group it by order_number because in one order there
can be more persons.

In tools like the infomaker I can do this with a crosstab, but how can I
do this with a sql in ianywhere???

Thanks a lot if anyone can help me.


Paul Horan[TeamSybase] Posted on 2003-12-20 16:59:30.0Z
From: "Paul Horan[TeamSybase]" <paulhATvcisolutionsDOTcom>
Newsgroups: ianywhere.public.general
References: <3fe44598@forums-1-dub>
Subject: Re: crosstab like in infomaker
Lines: 58
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: 67-23-182-204.bflony.adelphia.net
X-Original-NNTP-Posting-Host: 67-23-182-204.bflony.adelphia.net
Message-ID: <3fe47ff2$1@forums-1-dub>
Date: 20 Dec 2003 08:59:30 -0800
X-Trace: forums-1-dub 1071939570 67.23.182.204 (20 Dec 2003 08:59:30 -0800)
X-Original-Trace: 20 Dec 2003 08:59:30 -0800, 67-23-182-204.bflony.adelphia.net
X-Authenticated-User: TeamPS
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2217
Article PK: 4424

If the number of rows across are fixed, then the solution is fairly simple:

SELECT
trip_number,
order_number,
person_number,
sum(amount * (if description = 'doubleroom' then 1 else 0 endif)) as doubleroom,
sum(amount * (if description = 'insurance' then 1 else 0 endif)) as insurance,
sum(amount * (if description = 'singleroom' then 1 else 0 endif)) as singleroom
FROM
theTable
GROUP BY
trip_number,
order_number,
person_number

We use this to simulate a crosstab report showing daily sales totals for the past three weeks (21 columns across)
grouped by SalesPerson. If you really want to have a variable number of columns across, then you're talking about
writing a multi-step stored procedure.

--
Paul Horan[TeamSybase]

"Martin Lungershausen" <martin@teleworker-center.com> wrote in message news:3fe44598@forums-1-dub...
> Can anyone help me making a crosstab like in inofmaker with ianywhere
> 8.x or 9.x
>
> I have different categories on each row
>
> like
>
> trip_number order_number pers_number description amount
> 999 1 1 doubleroom 1
> 999 1 1 insurance 1
> 999 1 1 singleroom 0
> 999 2 2 doubleroom 0
> 999 2 2 insurance 0
> 999 2 2 singleroom 1
>
> and want to get in a report
>
> trip_number order_number pers_numer doubleroom insurance singleroom
> 999 1 1 1 1 0
> 999 2 2 0 0 1
>
> the amount of rows can change by each trip so in the report for a trip
> there can be different coloums (not in one report)
>
> and then I want to group it by order_number because in one order there
> can be more persons.
>
> In tools like the infomaker I can do this with a crosstab, but how can I
> do this with a sql in ianywhere???
>
> Thanks a lot if anyone can help me.
>