Converting rows to columns

[pnad]

I have data in 2 tables in the following manner:

Table A:

Source_Code BU_SET_ID Account_Number
ALS 3 1415999
ALS 1 1415999


Table B:

BU_SET_ID BU_type BU_Code
1 LOB TEST
3 REICO SURP

I need the o/p to look like:

Source_Code Account_Number LOB REICO
ALS 1415999 TEST


Any help is appreciated.

 

[SantaMufasa]

PNAD,

I have a couple of questions prior to suggesting a resolution to your need:

1) Should your "REICO" column have "SURP" appearing below it, or is there some logic that you didn't explain?

2) Is it correct that the joining of rows from the two tables occurs exclusively by matching values in the "BU_SET_ID" column?

3) Are the only possible "BU_TYPE" values, "LOB" and "REICO", or are there other values? If so, what are they, and do you want them to appear as output columns, as well?

4) Does a single "ACCOUNT_NUMBER" have multiple "SOURCE_CODE" values? Does a single "SOURCE_CODE" have multiple "ACCOUNT_NUMBER" values?

5) Will an output row
a) always have a "LOB" and a "REICO" value?
b) sometimes have either a "LOB" or a "REICO" value?
c) sometimes have neither a "LOB" nor a "REICO" value?




Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[pnad]

I should have been more clear. Please find my answers below:

1) REICO should have SURP. I forgot to type it the first time.

2) YES

3) There are other BU_TYPE values as well that need to appear. They are PROD, TAXST, OPC, SAD ,REICH, REICO, BCTR, STATE and the order in which they need to appear is LOB, PROD, TAXST, OPC, SAD, REICH, REICO, BCTR, STATE.

4) Different Feeder Systems(Source Codes) could send in multiple account numbers. So one account number can have multiple source codes and one source could definitely have multiple account numbers.

5) The output row will sometimes have neither LOB or any other REICO value but it could have other BU_TYPE's.

It gets better actually - sigh ! This needs to be linked to another table called Table C that has data as follows:

Table C:

TRAN_DETAIL_ID LOCATION_CODE LOCATION_TYPE
12344 AUTO POLCY
12355 PAC MEMO

The other Location type is TRX.

Table A links to Table C on the TRAN_DETAIL_ID column.

so the o/p should look like:
Source_Code AccountNumber POLCY LOB PROD TAXST OPC SAD REICH REICO BCTR TRX STATE

 

[pnad]

I used sub queries to fix the problem