SQL to go From N Rows to 1 Row and N Columns

25 pts.
Tags:
AS/400
DB2
SQL

I would like to ask for your help one more time, if you may. I work for an insurance company and am facing a challenge. I have two tables as such:

policy_table

Policy_number

12345

98765

45678

Policy_id (PK)

1

2

3

endorsement_table

Endorsement

flood

quake

theft

hurricane

freeze-pipe

fire

dog-bite

quake

flood

Policy_id (FK)

1

1

1

2

2

3

3

3

3

I would like to be able to write an SQL that would provide me the following result:

Endorsement1

Endorsement2

Endorsement3

Endorsement4

flood

quake

theft

hurricane

freeze-pipe

fire

dog-bite

quake

flood

Policy_number

12345

98765

45678

So, basically I am trying to go from n rows to 1 row with n columns.

Is this possible?

(This is almost like building a pivot table but not knowing the exact amount of columns will have the result table, or in other words a dynamic column build.)

I am running DB2 on the IBM i platform (formerly AS400)



Software/Hardware used:
Running on V7R1

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • CharlieBrowne
    What is linking the policy table to the endorsement table?
    Is the endorsement table just a list of the types of endorsement. Thus a single record per endorsement?
    41,370 pointsBadges:
    report
  • Esdras

    My apologies, the table did not display well, let me try to again:

    Table: POLICY_TABLE

    POLICY_ID (pk)        POLICY_NUMBER

    ---------------------        --------------------------

    1                                12345

    2                                 98765

    3                                 45678

     

    Table: ENDORSEMENT_TABLE

    POLICY_ID (FK)            ENDORSEMENT

    -----------------------           ------------------------

    1                                     FLOOD

    1                                     QUAKE

    1                                     THEFT

    2                                      HURRICANE

    2                                       FREEZE-PIPE

    3                                       FIRE

    3                                       DOG-BITE

    3                                       QUAKE

    3                                       FLOOD

     

    They are linked by the POLICY_ID field.

     

    Thanks,

    Esdras

     

    25 pointsBadges:
    report
  • Esdras

    The result that I am looking for is:

    POLICY_NUMBER    ENDORSEMENT   ENDORSEMENT    ENDORSEMENT   ENDORSE

    -------------------------    -----------------------    -----------------------    ------------------------   ---------------

    12345                         FLOOD                    QUAKE                  THEFT

    98765                         HURRICANE           FREEZE-PIPE

    45678                         FIRE                         DOG-BITE             QUAKE                      FLOOD

    25 pointsBadges:
    report
  • CharlieBrowne
    I believe this would take some work using SQL.
    I would create a new file: POL#, end1, end2, end3, ...
    Then write an RPG program to Read Policy file and do a reade loop in the endorsement file to populate the new file.
    Then send the new file out as a .csv file to user.
    41,370 pointsBadges:
    report
  • philpl1jb

    This is painfull.  I would do it like CharlieBrowne but since you asked

    1.  Create a table (MyTable, POLICY_ID , POLICY_NUMBER, Endor1, Endor2, Endor3 ... etc

    2.  Load first three fields of table 

    Insert into mytable

    POLICY_ID , POLICY_NUMBER, Endor1

    (pt.POLICY_ID , pt.POLICY_NUMBER, min(et.ENDORESEMENT)

    from POLICY_TABLE pt join ENDORSEMENT_TABLE et

    on pt.POLICY_ID = et.POLICY_ID

    Group by pt.POLICY_ID , pt.POLICY_NUMBER;

    That would load the first three columns, using the min causes it to put the lowest alpha value in Endor1.

    Then a second SQL would be used to update the file finding the min values that aren't in Endor1 and loading them into Endor2.

    Roughly repeating the second SQL you can populate Endor3, etc.

    Good luck

     

    49,830 pointsBadges:
    report
  • carlosdl
    The problem with creating a new file or table, is that you don't know how many columns you will need.

    @Esdras:  Why do you need a result like that? How are you going to use that result?
    69,160 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following