Skip to Content
avatar image
Former Member

Select from vertical to horizontal ??

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Jul 06, 2009 at 08:24 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jul 06, 2009 at 08:28 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • > 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