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.

From Rows To Columns

5 posts in General Discussion Last posting was on 2009-12-11 22:34:04.0Z
RGS Posted on 2009-12-11 17:21:32.0Z
Sender: 6b18.4b227e40.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: From Rows To Columns
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b227f9c.6b43.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 11 Dec 2009 09:21:32 -0800
X-Trace: forums-1-dub 1260552092 10.22.241.41 (11 Dec 2009 09:21:32 -0800)
X-Original-Trace: 11 Dec 2009 09:21:32 -0800, 10.22.241.41
Lines: 24
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28759
Article PK: 78001

Hi friends how are you!!

I have the next problem:

I have a table like:

CUSTOMER ID_PHONE PHONE
-----------------------------
1 1 34534534
1 2 45645645
2 1 11434434
3 1 345345345
3 2 767676767
3 3 232232323

I need to translate this data to a table like:

CUSTOMER PHONE_1 PHONE_2 PHONE_3
-----------------------------------------

How can I do that but wihtout while's or cursors? (because
perfomance is very important in this batch process

Thank's!!!


"Mark A. Parsons" <iron_horse Posted on 2009-12-11 17:34:24.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: From Rows To Columns
References: <4b227f9c.6b43.1681692777@sybase.com>
In-Reply-To: <4b227f9c.6b43.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 091201-0, 12/01/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b2282a0$1@forums-1-dub>
Date: 11 Dec 2009 09:34:24 -0800
X-Trace: forums-1-dub 1260552864 10.22.241.152 (11 Dec 2009 09:34:24 -0800)
X-Original-Trace: 11 Dec 2009 09:34:24 -0800, vip152.sybase.com
Lines: 31
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28760
Article PK: 78002

Given the sample data, what's the expected result set suppose to look like?

What query(s) have you tried so far?

Is the PHONE column a numeric or string?

RGS wrote:
> Hi friends how are you!!
>
> I have the next problem:
>
> I have a table like:
>
> CUSTOMER ID_PHONE PHONE
> -----------------------------
> 1 1 34534534
> 1 2 45645645
> 2 1 11434434
> 3 1 345345345
> 3 2 767676767
> 3 3 232232323
>
> I need to translate this data to a table like:
>
> CUSTOMER PHONE_1 PHONE_2 PHONE_3
> -----------------------------------------
>
> How can I do that but wihtout while's or cursors? (because
> perfomance is very important in this batch process
>
> Thank's!!!


RGS Posted on 2009-12-11 18:30:25.0Z
Sender: 6b18.4b227e40.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: Re: From Rows To Columns
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b228fc1.6c7c.1681692777@sybase.com>
References: <4b2282a0$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 11 Dec 2009 10:30:25 -0800
X-Trace: forums-1-dub 1260556225 10.22.241.41 (11 Dec 2009 10:30:25 -0800)
X-Original-Trace: 11 Dec 2009 10:30:25 -0800, 10.22.241.41
Lines: 50
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28761
Article PK: 78005

Hi friend

Data should look like:

CUSTOMER PHONE_1 PHONE_2 PHONE_3
-----------------------------------------
1 34534534 45645645 null
2 11434434 null null
3 345345345 767676767 232232323

Field PHONE is VARCHAR(20)

I need ideas about how can I do my query's (with temporally
tables por example) for avoid the use of cursors or while
sentence

Thanks!!!

> Given the sample data, what's the expected result set
> suppose to look like?
>
> What query(s) have you tried so far?
>
> Is the PHONE column a numeric or string?
>
> RGS wrote:
> > Hi friends how are you!!
> >
> > I have the next problem:
> >
> > I have a table like:
> >
> > CUSTOMER ID_PHONE PHONE
> > -----------------------------
> > 1 1 34534534
> > 1 2 45645645
> > 2 1 11434434
> > 3 1 345345345
> > 3 2 767676767
> > 3 3 232232323
> >
> > I need to translate this data to a table like:
> >
> > CUSTOMER PHONE_1 PHONE_2 PHONE_3
> > -----------------------------------------
> >
> > How can I do that but wihtout while's or cursors?
> > (because perfomance is very important in this batch
> > process
> > Thank's!!!


Leonid Gvirtz Posted on 2009-12-11 21:19:08.0Z
From: Leonid Gvirtz <lgvirtz@yahoo.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.5) Gecko/20091204 Thunderbird/3.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: From Rows To Columns
References: <4b2282a0$1@forums-1-dub> <4b228fc1.6c7c.1681692777@sybase.com>
In-Reply-To: <4b228fc1.6c7c.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b22b74c$1@forums-1-dub>
Date: 11 Dec 2009 13:19:08 -0800
X-Trace: forums-1-dub 1260566348 10.22.241.152 (11 Dec 2009 13:19:08 -0800)
X-Original-Trace: 11 Dec 2009 13:19:08 -0800, vip152.sybase.com
Lines: 76
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28762
Article PK: 78003

Hi

Provided that a customer can't have more than 3 numbers, you can use the
following query:

select CUSTOMER,
max(case when ID_PHONE = 1 then PHONE else null end) PHONE_1,
max(case when ID_PHONE = 2 then PHONE else null end) PHONE_2,
max(case when ID_PHONE = 3 then PHONE else null end) PHONE_3
from phones
group by CUSTOMER
order by CUSTOMER
go

If the count of numbers per customer is not limited then the task
becomes more difficult.

Hope it helps
Leonid Gvirtz
http://www.gvirtz-consulting.com

On 12/11/2009 8:30 PM, RGS wrote:
> Hi friend
>
> Data should look like:
>
> CUSTOMER PHONE_1 PHONE_2 PHONE_3
> -----------------------------------------
> 1 34534534 45645645 null
> 2 11434434 null null
> 3 345345345 767676767 232232323
>
> Field PHONE is VARCHAR(20)
>
> I need ideas about how can I do my query's (with temporally
> tables por example) for avoid the use of cursors or while
> sentence
>
> Thanks!!!
>
>
>> Given the sample data, what's the expected result set
>> suppose to look like?
>>
>> What query(s) have you tried so far?
>>
>> Is the PHONE column a numeric or string?
>>
>> RGS wrote:
>>
>>> Hi friends how are you!!
>>>
>>> I have the next problem:
>>>
>>> I have a table like:
>>>
>>> CUSTOMER ID_PHONE PHONE
>>> -----------------------------
>>> 1 1 34534534
>>> 1 2 45645645
>>> 2 1 11434434
>>> 3 1 345345345
>>> 3 2 767676767
>>> 3 3 232232323
>>>
>>> I need to translate this data to a table like:
>>>
>>> CUSTOMER PHONE_1 PHONE_2 PHONE_3
>>> -----------------------------------------
>>>
>>> How can I do that but wihtout while's or cursors?
>>> (because perfomance is very important in this batch
>>> process
>>> Thank's!!!
>>>


RGS Posted on 2009-12-11 22:34:04.0Z
Sender: 6efd.4b22abcb.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: Re: From Rows To Columns
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b22c8db.7243.1681692777@sybase.com>
References: <4b22b74c$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 11 Dec 2009 14:34:04 -0800
X-Trace: forums-1-dub 1260570844 10.22.241.41 (11 Dec 2009 14:34:04 -0800)
X-Original-Trace: 11 Dec 2009 14:34:04 -0800, 10.22.241.41
Lines: 83
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28763
Article PK: 78004

Excelent!!!

It works!!!!

> Hi
>
> Provided that a customer can't have more than 3 numbers,
> you can use the following query:
>
> select CUSTOMER,
> max(case when ID_PHONE = 1 then PHONE else null
> end) PHONE_1,
> max(case when ID_PHONE = 2 then PHONE else null
> end) PHONE_2,
> max(case when ID_PHONE = 3 then PHONE else null
> end) PHONE_3 from phones
> group by CUSTOMER
> order by CUSTOMER
> go
>
> If the count of numbers per customer is not limited then
> the task becomes more difficult.
>
> Hope it helps
> Leonid Gvirtz
> http://www.gvirtz-consulting.com
>
> On 12/11/2009 8:30 PM, RGS wrote:
> > Hi friend
> >
> > Data should look like:
> >
> > CUSTOMER PHONE_1 PHONE_2 PHONE_3
> > -----------------------------------------
> > 1 34534534 45645645 null
> > 2 11434434 null null
> > 3 345345345 767676767 232232323
> >
> > Field PHONE is VARCHAR(20)
> >
> > I need ideas about how can I do my query's (with
> > temporally tables por example) for avoid the use of
> > cursors or while sentence
> >
> > Thanks!!!
> >
> >
> >> Given the sample data, what's the expected result set
> >> suppose to look like?
> >>
> >> What query(s) have you tried so far?
> >>
> >> Is the PHONE column a numeric or string?
> >>
> >> RGS wrote:
> >>
> >>> Hi friends how are you!!
> >>>
> >>> I have the next problem:
> >>>
> >>> I have a table like:
> >>>
> >>> CUSTOMER ID_PHONE PHONE
> >>> -----------------------------
> >>> 1 1 34534534
> >>> 1 2 45645645
> >>> 2 1 11434434
> >>> 3 1 345345345
> >>> 3 2 767676767
> >>> 3 3 232232323
> >>>
> >>> I need to translate this data to a table like:
> >>>
> >>> CUSTOMER PHONE_1 PHONE_2 PHONE_3
> >>> -----------------------------------------
> >>>
> >>> How can I do that but wihtout while's or cursors?
> >>> (because perfomance is very important in this batch
> >>> process
> >>> Thank's!!!
> >>>
>