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.

Pivot Table!!

2 posts in Windows NT Last posting was on 2000-09-05 21:29:34.0Z
Anonymous Posted on 2000-09-05 17:18:46.0Z
From: Anonymous@sybase.com
Date: Tue, 5 Sep 2000 13:18:46 -0400
Newsgroups: sybase.public.sqlserver.nt
Subject: Pivot Table!!
Message-ID: <8CE389EE28D73F5A005F1A3885256951.005F1A4885256951@webforums>
Lines: 15
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.sqlserver.nt:1673
Article PK: 1089146

Help-

We're running ASE 11.5.1 (soon to upgrade to 11.9.2) and I'm trying to
create a crosstab query/pivot table and getting nowhere. I've tried
variations on the following code and keep getting INVALID SYNTAX errors:

Select date,
Sum(if hour = '01:00' then price else '0' endif) as Hour_1,
Sum(if hour = '02:00' then price else '0' endif) as Hour_2, ...

which I took off of a Sybase forum. Which version starts supporting the
above syntax? Does anyone know of another way to create such a table via
SQL? I'm passing the connection and SQL to a DLL which calls the DB and
creates a spreadsheet. The client wants the data in columns, one row per
day, instead of one row per day/hour.


Bret Halford Posted on 2000-09-05 21:29:34.0Z
Message-ID: <39B565BE.DF42FFD6@sybase.com>
Date: Tue, 05 Sep 2000 15:29:34 -0600
From: Bret Halford <bret@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.5 [en]C-CCK-MCD (WinNT; I)
X-Accept-Language: en,ja
MIME-Version: 1.0
Subject: Re: Pivot Table!!
References: <8CE389EE28D73F5A005F1A3885256951.005F1A4885256951@webforums>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 27
NNTP-Posting-Host: bret-pc.sybase.com 157.133.80.211
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1671
Article PK: 1089142

Your example seems to be pseudo code.
This can be done in ASE 11.5 or higher using CASE statements.
Try:

Select date,
sum (CASE when hour = "01:00" then price else 0 END) as Hour_1,
sum (CASE when hour = "02:00" then price else 0 END) as Hour_2,
...

-bret

Anonymous@sybase.com wrote:

> Help-
>
> We're running ASE 11.5.1 (soon to upgrade to 11.9.2) and I'm trying to
> create a crosstab query/pivot table and getting nowhere. I've tried
> variations on the following code and keep getting INVALID SYNTAX errors:
>
> Select date,
> Sum(if hour = '01:00' then price else '0' endif) as Hour_1,
> Sum(if hour = '02:00' then price else '0' endif) as Hour_2, ...
>
> which I took off of a Sybase forum. Which version starts supporting the
> above syntax? Does anyone know of another way to create such a table via
> SQL? I'm passing the connection and SQL to a DLL which calls the DB and
> creates a spreadsheet. The client wants the data in columns, one row per
> day, instead of one row per day/hour.