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.

Horizontal "pivot" like presentation

8 posts in DataWindow Last posting was on 2008-09-16 05:15:56.0Z
William Shiers Posted on 2008-09-15 15:58:54.0Z
Sender: 55ff.48ce8547.1804289383@sybase.com
From: William Shiers
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Horizontal "pivot" like presentation
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <48ce863e.562d.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 15 Sep 2008 08:58:54 -0700
X-Trace: forums-1-dub 1221494334 10.22.241.41 (15 Sep 2008 08:58:54 -0700)
X-Original-Trace: 15 Sep 2008 08:58:54 -0700, 10.22.241.41
Lines: 19
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87757
Article PK: 417013

I am wondering the best means to present data in a
datawindow "horizontally". There's no need to create
count/sum/group information - It's simply to present it in a
horizontal fashion: i.e.:

Row1 Row2 Row3
Column1 A C B
Column2 B C C
Column3 C A A

The number of rows are not known at the time of
retrieval(i.e. can vary).

I've looked at n-up and cross-tab but - still can't seem to
get this down.

Any/all help is greatly appreciated.

Thanks!


fisher <fisher_NO Posted on 2008-09-15 16:50:56.0Z
From: fisher <fisher_NO@SPAM_star.wckp.lodz.pl_PLEASE>
User-Agent: Thunderbird 2.0.0.16 (Windows/20080708)
MIME-Version: 1.0
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Horizontal "pivot" like presentation
References: <48ce863e.562d.1681692777@sybase.com>
In-Reply-To: <48ce863e.562d.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 080914-0, 09/14/2008), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48ce9270$1@forums-1-dub>
Date: 15 Sep 2008 09:50:56 -0700
X-Trace: forums-1-dub 1221497456 10.22.241.152 (15 Sep 2008 09:50:56 -0700)
X-Original-Trace: 15 Sep 2008 09:50:56 -0700, vip152.sybase.com
Lines: 30
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87758
Article PK: 417014

I wish there'd be something like this.
There's an enhancement on ISUG (I think it's 2547)you can vote for. I
believe it was also mentioned number of ties while request were
discussed in future-discussion forums.

I'd even accept report like (non-editable) version at the beginning...

Have you tried with max function? It could help if you have unique pairs
of values

William Shiers wrote:
> I am wondering the best means to present data in a
> datawindow "horizontally". There's no need to create
> count/sum/group information - It's simply to present it in a
> horizontal fashion: i.e.:
>
> Row1 Row2 Row3
> Column1 A C B
> Column2 B C C
> Column3 C A A
>
> The number of rows are not known at the time of
> retrieval(i.e. can vary).
>
> I've looked at n-up and cross-tab but - still can't seem to
> get this down.
>
> Any/all help is greatly appreciated.
>
> Thanks!


Ken Balakrishnan Posted on 2008-09-15 17:15:30.0Z
From: "Ken Balakrishnan" <kNOeSPAMn@cpsREMOVEME92.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <48ce863e.562d.1681692777@sybase.com>
Subject: Re: Horizontal "pivot" like presentation
Lines: 53
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48ce9832$1@forums-1-dub>
Date: 15 Sep 2008 10:15:30 -0700
X-Trace: forums-1-dub 1221498930 10.22.241.152 (15 Sep 2008 10:15:30 -0700)
X-Original-Trace: 15 Sep 2008 10:15:30 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87759
Article PK: 417015

Do you know ahead of time which table and which columns from the table you
care about? If so, then you can use a crosstab, as long as you have a table
or view that lists all the columns in the target table (it could be a system
table or a table you create, it just needs one row per column in the target
table). The SQL would look something like this (may need to be changed
depending on your DBMS):

select collist.column_name,
targettbl.row_identifier,
case when collist.column_name = 'COLUMN1'
then targettbl.column1
when collist.column_name = 'COLUMN2'
then targettbl.column2
...
when collist.column_name = 'COLUMNX'
then targettbl.columnX
end as columnval
from collist,
targettbl
where [criteria for collist if necessary],
[criteria for targettbl if necessary]

Then in the xtab definition, you use targettbl.row_identifier as the Columns
field, collist.column_name as the Rows field, and max(columnval for
crosstab) as the Values field.

HTH,
Ken

<William Shiers> wrote in message
news:48ce863e.562d.1681692777@sybase.com...
>I am wondering the best means to present data in a
> datawindow "horizontally". There's no need to create
> count/sum/group information - It's simply to present it in a
> horizontal fashion: i.e.:
>
> Row1 Row2 Row3
> Column1 A C B
> Column2 B C C
> Column3 C A A
>
> The number of rows are not known at the time of
> retrieval(i.e. can vary).
>
> I've looked at n-up and cross-tab but - still can't seem to
> get this down.
>
> Any/all help is greatly appreciated.
>
> Thanks!


William Shiers Posted on 2008-09-15 17:56:07.0Z
Sender: 55ff.48ce8547.1804289383@sybase.com
From: William Shiers
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Horizontal "pivot" like presentation
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <48cea1b7.5c63.1681692777@sybase.com>
References: <48ce9832$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 15 Sep 2008 10:56:07 -0700
X-Trace: forums-1-dub 1221501367 10.22.241.41 (15 Sep 2008 10:56:07 -0700)
X-Original-Trace: 15 Sep 2008 10:56:07 -0700, 10.22.241.41
Lines: 70
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87760
Article PK: 417016

Ken,

I do know the tables... we're using Oracle...

I apologize in advance but- I'm not sure how the target
table/column list go together...

If I'm selecting with a simple join between two tables
(Table A and Table B) with the expectation of the
"horizontal" view of data in the data window - can you apply
that in your example.

Thanks for your help!

> Do you know ahead of time which table and which columns
> from the table you care about? If so, then you can use a
> crosstab, as long as you have a table or view that lists
> all the columns in the target table (it could be a system
> table or a table you create, it just needs one row per
> column in the target table). The SQL would look
> something like this (may need to be changed depending on
> your DBMS):
>
> select collist.column_name,
> targettbl.row_identifier,
> case when collist.column_name = 'COLUMN1'
> then targettbl.column1
> when collist.column_name = 'COLUMN2'
> then targettbl.column2
> ...
> when collist.column_name = 'COLUMNX'
> then targettbl.columnX
> end as columnval
> from collist,
> targettbl
> where [criteria for collist if necessary],
> [criteria for targettbl if necessary]
>
> Then in the xtab definition, you use
> targettbl.row_identifier as the Columns field,
> collist.column_name as the Rows field, and max(columnval
> for crosstab) as the Values field.
>
> HTH,
> Ken
>
>
> <William Shiers> wrote in message
> news:48ce863e.562d.1681692777@sybase.com...
> >I am wondering the best means to present data in a
> > datawindow "horizontally". There's no need to create
> > count/sum/group information - It's simply to present it
> > in a horizontal fashion: i.e.:
> >
> > Row1 Row2 Row3
> > Column1 A C B
> > Column2 B C C
> > Column3 C A A
> >
> > The number of rows are not known at the time of
> > retrieval(i.e. can vary).
> >
> > I've looked at n-up and cross-tab but - still can't seem
> > to get this down.
> >
> > Any/all help is greatly appreciated.
> >
> > Thanks!
>
>


fisher <fisher_NO Posted on 2008-09-15 18:02:33.0Z
From: fisher <fisher_NO@SPAM_star.wckp.lodz.pl_PLEASE>
User-Agent: Thunderbird 2.0.0.16 (Windows/20080708)
MIME-Version: 1.0
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Horizontal "pivot" like presentation
References: <48ce9832$1@forums-1-dub> <48cea1b7.5c63.1681692777@sybase.com>
In-Reply-To: <48cea1b7.5c63.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 080914-0, 09/14/2008), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48cea339@forums-1-dub>
Date: 15 Sep 2008 11:02:33 -0700
X-Trace: forums-1-dub 1221501753 10.22.241.152 (15 Sep 2008 11:02:33 -0700)
X-Original-Trace: 15 Sep 2008 11:02:33 -0700, vip152.sybase.com
Lines: 74
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87761
Article PK: 417018

If you're using Oracle 11g you can use new feature pivot, but it
requires you to know all the values you want to build your pivot on.

Check oracle doc for details

William Shiers wrote:
> Ken,
>
> I do know the tables... we're using Oracle...
>
> I apologize in advance but- I'm not sure how the target
> table/column list go together...
>
> If I'm selecting with a simple join between two tables
> (Table A and Table B) with the expectation of the
> "horizontal" view of data in the data window - can you apply
> that in your example.
>
> Thanks for your help!
>
>> Do you know ahead of time which table and which columns
>> from the table you care about? If so, then you can use a
>> crosstab, as long as you have a table or view that lists
>> all the columns in the target table (it could be a system
>> table or a table you create, it just needs one row per
>> column in the target table). The SQL would look
>> something like this (may need to be changed depending on
>> your DBMS):
>>
>> select collist.column_name,
>> targettbl.row_identifier,
>> case when collist.column_name = 'COLUMN1'
>> then targettbl.column1
>> when collist.column_name = 'COLUMN2'
>> then targettbl.column2
>> ...
>> when collist.column_name = 'COLUMNX'
>> then targettbl.columnX
>> end as columnval
>> from collist,
>> targettbl
>> where [criteria for collist if necessary],
>> [criteria for targettbl if necessary]
>>
>> Then in the xtab definition, you use
>> targettbl.row_identifier as the Columns field,
>> collist.column_name as the Rows field, and max(columnval
>> for crosstab) as the Values field.
>>
>> HTH,
>> Ken
>>
>>
>> <William Shiers> wrote in message
>> news:48ce863e.562d.1681692777@sybase.com...
>>> I am wondering the best means to present data in a
>>> datawindow "horizontally". There's no need to create
>>> count/sum/group information - It's simply to present it
>>> in a horizontal fashion: i.e.:
>>>
>>> Row1 Row2 Row3
>>> Column1 A C B
>>> Column2 B C C
>>> Column3 C A A
>>>
>>> The number of rows are not known at the time of
>>> retrieval(i.e. can vary).
>>>
>>> I've looked at n-up and cross-tab but - still can't seem
>>> to get this down.
>>>
>>> Any/all help is greatly appreciated.
>>>
>>> Thanks!
>>


Ken Balakrishnan Posted on 2008-09-15 19:38:04.0Z
From: "Ken Balakrishnan" <kNOeSPAMn@cpsREMOVEME92.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <48ce9832$1@forums-1-dub> <48cea1b7.5c63.1681692777@sybase.com>
Subject: Re: Horizontal "pivot" like presentation
Lines: 143
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48ceb99c@forums-1-dub>
Date: 15 Sep 2008 12:38:04 -0700
X-Trace: forums-1-dub 1221507484 10.22.241.152 (15 Sep 2008 12:38:04 -0700)
X-Original-Trace: 15 Sep 2008 12:38:04 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87763
Article PK: 417019

Well, once you add joins, computed fields, etc., it gets more complicated.
If you have an upperbound on the number of rows, it might be simpler and
easier to maintain to just create an external datawindow with X columns, and
then use a datastore to populate it manually and hide the columns you don't
need.

But anyway, here's the general idea:

Say your "target" SQL statement is:

select t1.ID ID,
t1.NAME NAME,
t2.DETAIL1 DETAIL1,
t2.DETAIL2 DETAIL2
from MASTERTBL t1,
DETAILTBL t2
where t1.ID = t2.ID;

Then for your "column list" table you could have:

select column_name
from sys.all_tab_columns
where owner = '[fill in owner name here]'
and table_name || '.' || column_name in
('MASTERTBL.ID',
'MASTERTBL.NAME',
'DETAILTBL.DETAIL1',
'DETAILTBL.DETAIL2')

(or you could create your own table, or UNION together a bunch of "Select
'...' from dual" statements -- you just need a query that will return one
row for each field that you want to have as a row in the report).

You'd plug these into my query above like so:

select collist.column_name,
targettbl.ID,
case when collist.column_name = 'ID'
then targettbl.ID
when collist.column_name = 'NAME'
then targettbl.NAME
when collist.column_name = 'DETAIL1'
then targettbl.DETAIL1
when collist.column_name = 'DETAIL2'
then targettbl.DETAIL2
end columnval
from (select column_name
from sys.all_tab_columns
where owner = '[fill in owner name here]'
and table_name || '.' || column_name in
('MASTERTBL.ID',
'MASTERTBL.NAME',
'DETAILTBL.DETAIL1',
'DETAILTBL.DETAIL2')
) collist,
(select t1.ID ID,
t1.NAME NAME,
t2.DETAIL1 DETAIL1,
t2.DETAIL2 DETAIL2
from MASTERTBL t1,
DETAILTBL t2
where t1.ID = t2.ID) targettbl

Ken

<William Shiers> wrote in message
news:48cea1b7.5c63.1681692777@sybase.com...
> Ken,
>
> I do know the tables... we're using Oracle...
>
> I apologize in advance but- I'm not sure how the target
> table/column list go together...
>
> If I'm selecting with a simple join between two tables
> (Table A and Table B) with the expectation of the
> "horizontal" view of data in the data window - can you apply
> that in your example.
>
> Thanks for your help!
>
>> Do you know ahead of time which table and which columns
>> from the table you care about? If so, then you can use a
>> crosstab, as long as you have a table or view that lists
>> all the columns in the target table (it could be a system
>> table or a table you create, it just needs one row per
>> column in the target table). The SQL would look
>> something like this (may need to be changed depending on
>> your DBMS):
>>
>> select collist.column_name,
>> targettbl.row_identifier,
>> case when collist.column_name = 'COLUMN1'
>> then targettbl.column1
>> when collist.column_name = 'COLUMN2'
>> then targettbl.column2
>> ...
>> when collist.column_name = 'COLUMNX'
>> then targettbl.columnX
>> end as columnval
>> from collist,
>> targettbl
>> where [criteria for collist if necessary],
>> [criteria for targettbl if necessary]
>>
>> Then in the xtab definition, you use
>> targettbl.row_identifier as the Columns field,
>> collist.column_name as the Rows field, and max(columnval
>> for crosstab) as the Values field.
>>
>> HTH,
>> Ken
>>
>>
>> <William Shiers> wrote in message
>> news:48ce863e.562d.1681692777@sybase.com...
>> >I am wondering the best means to present data in a
>> > datawindow "horizontally". There's no need to create
>> > count/sum/group information - It's simply to present it
>> > in a horizontal fashion: i.e.:
>> >
>> > Row1 Row2 Row3
>> > Column1 A C B
>> > Column2 B C C
>> > Column3 C A A
>> >
>> > The number of rows are not known at the time of
>> > retrieval(i.e. can vary).
>> >
>> > I've looked at n-up and cross-tab but - still can't seem
>> > to get this down.
>> >
>> > Any/all help is greatly appreciated.
>> >
>> > Thanks!
>>
>>


Jeremy Lakeman Posted on 2008-09-16 05:15:56.0Z
From: Jeremy Lakeman <jeremy.lakeman@gmail.com>
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Horizontal "pivot" like presentation
Date: Mon, 15 Sep 2008 22:15:56 -0700 (PDT)
Organization: http://groups.google.com
Lines: 31
Message-ID: <5542ac63-1d04-4598-b9b6-a8ee52e6ff07@m73g2000hsh.googlegroups.com>
References: <48ce863e.562d.1681692777@sybase.com>
NNTP-Posting-Host: 203.122.242.105
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1221542157 12286 127.0.0.1 (16 Sep 2008 05:15:57 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Tue, 16 Sep 2008 05:15:57 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: m73g2000hsh.googlegroups.com; posting-host=203.122.242.105; posting-account=euaBtgoAAAC_vDxfsxmpMKlBxHaHpmRS
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.0.1) Gecko/2008070208 Firefox/3.0.1,gzip(gfe),gzip(gfe)
X-HTTP-Via: 1.1 fly.essential.com.au:3128 (squid/2.5.STABLE8), 1.0 asp.essential.intranet:3128 (squid/2.5.STABLE8)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.yul.equant.net!nntp1.roc.gblx.net!nntp.gblx.net!nntp.gblx.net!nlpi057.nbdc.sbc.com!prodigy.net!border1.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!m73g2000hsh.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87764
Article PK: 417020


On Sep 16, 12:58 am, William Shiers wrote:
> I am wondering the best means to present data in a
> datawindow "horizontally".  There's no need to create
> count/sum/group information - It's simply to present it in a
> horizontal fashion: i.e.:
>
>             Row1              Row2              Row3
> Column1     A                 C                 B
> Column2     B                 C                 C
> Column3     C                 A                 A
>
> The number of rows are not known at the time of
> retrieval(i.e. can vary).
>
> I've looked at n-up and cross-tab but - still can't seem to
> get this down.
>
> Any/all help is greatly appreciated.
>
> Thanks!

I'd use an n-up myself. But I'd rebuild it on the fly based on the
number of columns you need.
The base dataobject would contain a template object for each column
with a tag value of "COPY" and a function would then duplicate this
object across the datawindow modifying the x and row_in_detail
properties.