Select from vertical to horizontal ??

[Former Member]

Hello Everybody

I use maxdb 7.6

I have a table "standardinfos" with default infos.(customer = 9911)

I have another table "customerinfos" with variable special infos.

The second table has columns fieldname,fieldvalue, linkkey

When doing a left join from standardinfos to customer infos i get a result with 1 to n rows (4 rows in the following example)

Now i need the records from table/cursor customerinfos in one record

Fieldname Fieldvalue Linkkey

F1 xyz 9911

F2 123 9911

F3 Blah 9911

F4 4711 9911

The wanted result should should look like:

F1 F2 F3 F4 linkkey

xyz 123 Blah 4711 9911

Is there an SQL command to get the wanted result ???

Any help welcomed

Best regards

Albert

[lbreddemann]

Hi again Albert,

just to make some additional remarks.

The problem here is the attribute-value-data-"design".

It's plain crap in 99.99% of the cases.

It's a no-data-design way to pump data into the database.

You cannot index the data properly. You have - obviously - trouble in selecting the data.

There's no easy way to implement constraints.

From a RDBMS point of view this is the worst approach you can take.

You may be interested in others point of view to this... [look here|http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10678084117056]

Cheers, Lars

[Former Member]

Hello Lars

Thank you for your answer!

We have an helpdesk/ticket system for different customers.

We have our default ticket data for each ticket in a default table.

(ticketid,incident,date,time,priority,...) nearly 100 fields.

But our customers are big companies(VW,BMW,Deutsche Bank,...)

and they force us to use and maintain there individual data.

VW could work with our default data but needs 2 special extra fields.

BMW could work with our default data but needs 9 special extra fields.

...

Don't cry, do it or you don't get the contract !

Never ending story.

We thought about storing the data in individual customer tables and writing programms

with dynamic tablenames for each customer,but then we chose this way.

We have all default fields in one part of the screen, and maintain individual data in a customer grid,

shown in another part of the screen.

This grid as 0 to n rows with individual data.

Only 5% of our customers do not need the individual data part!!!

We solved the problem by creating an dynamic select command from the result of:

select fieldname,fieldvalue from customerinfos where linkkey = xyz

the dynamic select looks like:

select defaultdata.*,i1.fieldvalue as "F1",i2.fieldvalue as "F2",i3.fieldvalue as "F3",....

from defaultdata,customerinfos as i1,customerinfos as i2,customerinfos as i3,.....

where defaultdat.id = ?l_id

and i1.linkkey = ?l_id and i1,fieldname = "F1"

and i2.linkkey = ?l_id and i2,fieldname = "F2"

and i3.linkkey = ?l_id and i3,fieldname = "F3"

...

Of course the fieldnames are not "F1","F2",...

They are variable too.(zuständig kunde,bereichsleiter kunde,kostenstelle1 kunde,kostenstelle2 kunde,

inventarid kunde, anschaffungsdatum kunde ,pagernummer hausmeister,öffnungszeit,............)

The select works as expected and returns a one record result cursor with the needed information.

Nevertheless the dynamic select may be not the best way, until now we found no other solotion.

Thank you again

Best regards

Albert

[lbreddemann]

> But our customers are big companies(VW,BMW,Deutsche Bank,...)

> and they force us to use and maintain there individual data.

>

> VW could work with our default data but needs 2 special extra fields.

> BMW could work with our default data but needs 9 special extra fields.

> ...

> Don't cry, do it or you don't get the contract !

That's how this business works. That's how it works for SAP as well.

> We thought about storing the data in individual customer tables and writing programms

> with dynamic tablenames for each customer,but then we chose this way.

Well, sorry to tell you, but: wrong decision.

> We have all default fields in one part of the screen, and maintain individual data in a customer grid,

> shown in another part of the screen.

> This grid as 0 to n rows with individual data.

> Only 5% of our customers do not need the individual data part!!!

But it looks like as if these 5% are the really important ones, are they?

> We solved the problem by creating an dynamic select command from the result of:

> select fieldname,fieldvalue from customerinfos where linkkey = xyz

> the dynamic select looks like:

>

> select defaultdata.*,i1.fieldvalue as "F1",i2.fieldvalue as "F2",i3.fieldvalue as "F3",....

> from defaultdata,customerinfos as i1,customerinfos as i2,customerinfos as i3,.....

> where defaultdat.id = ?l_id

> and i1.linkkey = ?l_id and i1,fieldname = "F1"

> and i2.linkkey = ?l_id and i2,fieldname = "F2"

> and i3.linkkey = ?l_id and i3,fieldname = "F3"

> ...

> The select works as expected and returns a one record result cursor with the needed information.

> Nevertheless the dynamic select may be not the best way, until now we found no other solotion.

As I wrote: with the dynamic approach you cannot do indexing, constraints checking or even choose the correct data types.

From what you wrote I understand that the query and the expected columns would be constant for every customer (so it's not that dynamic after all).

In that case, why don't you create a join-view that combines your standardfields and the customer specific fields?

You may then use this view as a base table for your transaction.

regards,

Lars

[lbreddemann]

Hi Albert,

what you want to archive is called "pivoting" a set of rows.

To do that you need to know the number of columns your result will have in advance.

Then you can do e.g. something like the following.

By the way: I would it consider a nice thing if you would provide the appropriate statements to setup your test case - this would leave me more time to give answers.

create table customerinfos (fieldname varchar(2) primary key, fieldvalue varchar(10), linkkey integer)
//
insert into customerinfos values ('F1', 'xyz', 9911)
//
insert into customerinfos values ('F2', '123', 9911)
//
insert into customerinfos values ('F3', 'Blah', 9911)
//
insert into customerinfos values ('F4', '4711', 9911)
//

select * from customerinfos
//
| FIELDNAME | FIELDVALUE | LINKKEY        |
| --------- | ---------- | -------------- |
| F1        | xyz        |           9911 |
| F2        | 123        |           9911 |
| F3        | Blah       |           9911 |
| F4        | 4711       |           9911 |
4 rows selected (843 usec)

//
select max (F1) as F1, max (F2) as F2, max (F3) as F3, max (F4) as F4, max (linkkey) as linkkey from 
(
select fieldvalue as F1, NULL as F2, NULL as F3, NULL as F4, linkkey from
customerinfos where fieldname='F1' and linkkey=9911
union all
select NULL as F1, fieldvalue as F2, NULL as F3, NULL as F4, linkkey from
customerinfos where fieldname='F2' and linkkey=9911
union all
select NULL as F1, NULL as F2, fieldvalue  as F3, NULL as F4, linkkey from
customerinfos where fieldname='F3' and linkkey=9911
union all
select NULL as F1, NULL as F2, NULL as F3, fieldvalue  as F4, linkkey from
customerinfos where fieldname='F4' and linkkey=9911
)
| F1         | F2         | F3         | F4         | LINKKEY        |
| ---------- | ---------- | ---------- | ---------- | -------------- |
| xyz        | 123        | Blah       | 4711       |           9911 |
1 row selected (1820 usec)

If you want to do this dynamically I've to disappoint you, there's no built-in way to do that.

One option might be to write a stored procedure that returns a cursor.

In that stored procedure you'd have to figure out the number of columns first and then build a statement like the one above on the fly.

Cheers,

Lars