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.

Want to write SQL which can gave me result like cross tab data window

3 posts in DataWindow Last posting was on 2008-05-02 18:43:57.0Z
Jenny Posted on 2008-05-01 20:33:52.0Z
Reply-To: "Jenny" <cs121@hotmail.com>
From: "Jenny" <cs121@hotmail.com>
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Want to write SQL which can gave me result like cross tab data window
Lines: 16
Organization: CA
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <481a2930$1@forums-1-dub>
Date: 1 May 2008 13:33:52 -0700
X-Trace: forums-1-dub 1209674032 10.22.241.152 (1 May 2008 13:33:52 -0700)
X-Original-Trace: 1 May 2008 13:33:52 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:86758
Article PK: 416017

Want to write SQL which can gave me result like cross tab data window



I have cross tab data window and it works fine. Now at some other place we
need same formatted report from SQL.



Any clue how I can write SQL which give result like cross tab datawindow



Can I replace this with some SQL? Or stored procedure.


"Paul Horan[TeamSybase]" Posted on 2008-05-02 01:58:43.0Z
From: "Paul Horan[TeamSybase]" <phoran AT sybase DOT com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <481a2930$1@forums-1-dub>
Subject: Re: Want to write SQL which can gave me result like cross tab data window
Lines: 66
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <481a7553$1@forums-1-dub>
Date: 1 May 2008 18:58:43 -0700
X-Trace: forums-1-dub 1209693523 10.22.241.152 (1 May 2008 18:58:43 -0700)
X-Original-Trace: 1 May 2008 18:58:43 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:86759
Article PK: 416019


"Jenny" <cs121@hotmail.com> wrote in message news:481a2930$1@forums-1-dub...
> Want to write SQL which can gave me result like cross tab data window
>
>
>
> I have cross tab data window and it works fine. Now at some other place we
> need same formatted report from SQL.
>
>
>
> Any clue how I can write SQL which give result like cross tab datawindow
>
>
>
> Can I replace this with some SQL? Or stored procedure.
>
>

It depends on the result set you want to return, and the DBMS you're
using... Provide us some sample DDL (table create statements) and the query
you're thinking of, and we'll see if we can help.

Here's an overly simplified example (for SQL Anywhere):

Create table salesperson(
salesperson_id integer not null,
salesperson_name varchar(255),
primary key( salesperson_id ) );

Create table orders(
order_id integer not null,
salesperson_id not null,
product_id not null,
sales_date date not null,
quantity integer not null,
unit_price decimal
primary key (order_id) );

A cross-tab query that would show 12 months of sales data across, grouped by
salesperson would be:

Select
salesperson_id,
salesperson_name,
sum( (unit_price * quantity) * (if sales_date between '2007-01-01' and
'2007-01-31' then 1 else 0 endif)) as jan07_sales,
sum( (unit_price * quantity) * (if sales_date between '2007-02-01' and
'2007-02-29' then 1 else 0 endif)) as feb07_sales,
sum( (unit_price * quantity) * (if sales_date between '2007-03-01' and
'2007-03-31' then 1 else 0 endif)) as mar07_sales,
...
sum( (unit_price * quantity) * (if sales_date between '2007-12-01' and
'2007-12-31' then 1 else 0 endif)) as dec07_sales,
from
orders join salesperson on orders.salesperson_id =
salesperson.salesperson_id
where
sales_date between '2007-01-01' and '2007-12-31'
group by
salesperson_id, salesperson_name
order by
salesperson_id ;

Paul Horan[TeamSybase]


Jenny Posted on 2008-05-02 18:43:57.0Z
Reply-To: "Jenny" <cs121@hotmail.com>
From: "Jenny" <cs121@hotmail.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <481a2930$1@forums-1-dub>
Subject: Re: Want to write SQL which can gave me result like cross tab data window
Lines: 38
Organization: CA
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <481b60ed$1@forums-1-dub>
Date: 2 May 2008 11:43:57 -0700
X-Trace: forums-1-dub 1209753837 10.22.241.152 (2 May 2008 11:43:57 -0700)
X-Original-Trace: 2 May 2008 11:43:57 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:86764
Article PK: 416023

want to write SQL or Stored Prcedure to get result like Cross Tab

see my present queary is below :

SELECT dbo_Main.YR_RECD AS Yr_Recd, dbo_Main.MNTH_RECD AS Mnth_Recd,

dbo_Main.SubStandGrp, SubGroupDesc.SubGroupDesc, dbo_Main.SubMethod,

SubMethodDesc.SubMethodDesc, Int((dbo_Main.[MNTH_RECD]-1)/3)+1 AS quarter,

Int((dbo_Main.[MNTH_RECD]-1)/6)+1 AS semiannual FROM (dbo_Main LEFT JOIN
SubGroupDesc ON dbo_Main.SubStandGrp =

SubGroupDesc.SubGroupCode) LEFT JOIN SubMethodDesc ON dbo_Main.SubMethod =
SubMethodDesc.SubMethodCode WHERE (((dbo_Main.Desc2)='FORMS'));

can i convert it like crosstab ?

"Jenny" <cs121@hotmail.com> wrote in message news:481a2930$1@forums-1-dub...
> Want to write SQL which can gave me result like cross tab data window
>
>
>
> I have cross tab data window and it works fine. Now at some other place we
> need same formatted report from SQL.
>
>
>
> Any clue how I can write SQL which give result like cross tab datawindow
>
>
>
> Can I replace this with some SQL? Or stored procedure.
>
>