Retrieve Rows as Columns



IBM's flagship relational database management system

Retrieve Rows as Columns

Postby mainframe_novice » Wed Apr 28, 2010 9:26 pm

I Have following tables

Table 1:-
Auto_Service
Auto_Id
Charge_Id

Table 2:-
Charge_Details
Charge_id
Amount

In Charge_Details table the tax percentage is also stored as it is a reference table

Let's say the values in Charge_Details are
Charge_Id Charge_dt Amount
1 labor 10
2 OilChange 30

I want the output as
Auto_Id Charge_dt amount labor
1 oilchange 30 10


Can someone please help with this . Here Labor was a row in input but in output it is a column
mainframe_novice
 
Posts: 35
Joined: Tue Apr 27, 2010 1:29 am
Has thanked: 0 time
Been thanked: 0 time

Re: Retrieve Rows as Columns

Postby dick scherrer » Wed Apr 28, 2010 11:37 pm

Hello,

You need to post a more comprehensive set of sample data and explain the rules of the process. Why should those 2 amounts be output with the "oilchange"? You "know" but how should the code know? What about all of the other rows n the table(s)?

Suggest you also practice with the "Code" tag to preserve alignment and improve readability. Use Preview to see your post as it will appear to the forum. Submit when you have the appearance the way you want.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Retrieve Rows as Columns

Postby mainframe_novice » Thu Apr 29, 2010 9:04 am

[b]Table 1:-
Auto_Service[/b]
Auto_Id
service_Id

[b]Table 2:-
Service_Details[/b]
Serv_id
Amount
serv_detl

In Charge_Details table the tax percentage is also stored as it is a reference table

Let's say the values in Charge_Details are
[b]Serv_id            serv_detl              Amount[/b]
1                          labor                         10
2                          OilChange                  30

I want the output as
[b]Auto_Id       serv_detl            amount               labor[/b]
1                     oilchange              30                      10



Each Service will have the same labor charge .
Labor is row in input and I want to display it as column .
mainframe_novice
 
Posts: 35
Joined: Tue Apr 27, 2010 1:29 am
Has thanked: 0 time
Been thanked: 0 time

Re: Retrieve Rows as Columns

Postby dick scherrer » Thu Apr 29, 2010 10:00 am

Hello,

To repeat:
Use Preview to see your post as it will appear to the forum. Submit when you have the appearance the way you want.
Obviously, you did not take the time to Preview. . .

Look at your post and notice how id is nearly impossible to read. . .

Also, you need to post more data. Show several services and the correspopnding output. The scrap posted is not enough for someone to use to help you. You have an idea of what you have and what you want from it. We do not.

When using the "Code" tag, do not try to use other tags.

How will each service will have the same labor charge? An oil change does not have the same charge as a transmission replacement. . .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post