Re: SQL to transform Rows to Columns

Haven't tested it yet. But looking at the SQL, seems like it would
result in the row value of feature field get transform into column in
ascending order. Yes, the left outer join will cause Min2 and Min3 to
be null if there is only 1 record for a particular phone number. You
could use COALESCE keyword to change it to blank if needed.



"Pete Helgren" <Pete@xxxxxxxxxx> wrote in message
news:<mailman.2942.1218466773.2545.midrange-l@xxxxxxxxxxxx>...

Lim,

Thanks for that. What I am having trouble understanding in this case

is

how were you accessing each record?

If you had records like this:
Phone Feature
5551212 SomeFeature
5551212 AnotherFeature
5551212 LastFeature

And ended up with:

Phone Min1 Min2 Min3
5551212,SomeFeature,AnotherFeature,LastFeature

Using the Min function, were you ordering the data so that Min

returned

the next logically ordered field? What if you had only 1 record, did
you end up with blanks/nulls in Min2, Min3?

Interesting.

Pete


Lim Hock-Chai wrote:

Pete,
I ask this similar question in RPG group and somebody (Not sure who,

I'm

going to lookup his name when I got a chance) came up with this sql
statement:


Please note that you will have to do 8 with statements, instead of

3. I

don't have time to re-do it using your file and fields. Basically,

my

file name is rmvFect1, which contains a phone number and a feature
fields. The result of this sql make a phone number with multi
records/features (up to 3) into one record with 3 features.



with first as (select phone as phone1,

Min(feature) as Min1 from rmvfect1 group by

phone),

second as (select phone as phone2,

Min(feature) as Min2 from rmvfect1 join first

on phone1 = phone and min1 < feature

group by phone),

Third as (select phone as phone3,

Min(feature) as Min3 from rmvfect1 join second

on phone2 = phone and min2 < feature

group by phone)

select phone1, min1, min2, min3

from first left outer join second on phone1 = phone2

left outer join third on phone1 = phone3

"Pete Helgren" <Pete@xxxxxxxxxx> wrote in message
news:<mailman.2780.1218317201.2545.midrange-l@xxxxxxxxxxxx>...

After perusing the web for an hour or so looking for a technique to

handle this, I haven't found a solution so perhaps some of the SQL

gurus

can lend a hand.

I have an application that has a select statement that basically

selects

9 columns of data from a single row in a table. Like this:

SELECT ASJD#, ASJAB1, ASJAB2, ASJAB3, ASJAB4, ASJAB5, ASJAB6,

ASJAB7,

ASJAB8 FROM MyFile

I also have two other databases that have similar data but the

table

structure is different. Rather than have a single record with 8

columns

of data (ASJAB1-8) it can have a recurring sequence of up to 8 rows

of

data. The database can vary by customer. I can't control what

database

they use. For simplicity sake, let's say they have two fields:

ID, ASJAB

This could repeat up to 8 times. So with data in the record

(again,

simplified) one record in one of the databases could be like this:

1234, 'stuff here', 'more stuff here', 'any stuff here', 'my stuff
here', 'other stuff here', 'could be stuff here', 'no stuff here',

'end

of stuff here'

but the other table in the other database might have data like

this:

1234, 'stuff here'
1234, 'more stuff here'
1234, 'any stuff here'
1234, 'my stuff here'
1234, 'other stuff here'
1234, 'could be stuff here'
1234, 'no stuff here'
1234, 'end of stuff here'

My goal is not not write database specific code.

Just to add an additional wrinkle. The rows *may* recur up to 8

times

but can vary from 0 to 8 occurrences. The fixed record *always* has

eight columns but the two column table with multiple occurrences in

the

rows can vary from 0 to 8. So, I almost think that I need a loop

to

loop through the results and "build" a record from that but I am

out

of

gas on my SQL skills at that point.

I *want* to handle this without changing the SQL statement in the
application. I would just like a view or procedure that returns a
record that "looks" the same to the application. The reason is

that

it

simplifies the SQL processing so I don't have to change all the SQL

statements in my application with a conditional statement that says

if

the table is "X" then do this. If it is "Y", then do this. If it

is

"Z" do it this way. A view or stored procedure that returned a

similar

record keeps it simple but may be impossible?

I am on V5R3M0. I am still poking around the web but I can't seem

to

find anything similar.

Thanks,

Pete