Changing columns to rows in oracle using unpivot

I got a requirement to join two tables and to get the desired rows that is to get the Enabled rows. One table is from the lookups and another from a custom table.

As there is no relationship between the two there is no possibility to join the tables. I now thought for some time to establish a relationship and started.

I first gave the same column names in custom table to the lookup values in description column. Now I have to transpose the columns to rows , In order to accomplish this I have used unpivot to match the values.

select * from ( SELECT lookup_code,meaning, description, enabled_flag
FROM fnd_lookup_values_vl
WHERE lookup_type = 'XX_POS_ISUP_ATACH_CAT'
AND enabled_flag = 'Y'
and trunc(sysdate) between trunc(START_DATE_ACTIVE) and trunc(nvl(END_DATE_ACTIVE,sysdate))) flv,
(SELECT * FROM (select bc_code, ec_attachment, TAXCARDEXPIRYDATEITM, cr_no_att, CLD_ATTACHMENT from XX_POS_ISUP_BUSCLASF_INFO_T where bc_code = 'OP')
UNPIVOT(
enabled_flag -- unpivot_clause
FOR attachment_columns -- unpivot_for_clause
IN ( -- unpivot_in_clause
ec_attachment AS 'ec_attachment',
TAXCARDEXPIRYDATEITM AS 'TAXCARDEXPIRYDATEITM',
cr_no_att AS 'cr_no_att',
CLD_ATTACHMENT as 'CLD_ATTACHMENT'
)
)) man_attach
where man_attach.attachment_columns = flv.desciption
and man_attach.enabled_flag = flv.enabled_flag

Here in the above query flv alias is fnd_lookup_values and man_attach alias is custom table.

Here I have used UNPIVOT and Im breakind down the elements in the query.

enabled_flag – the enabled field is the name used for values column

FOR attachment_columns – Name for column of the columns in table

IN ( — unpivot_in_clause
ec_attachment AS ‘ec_attachment’,
TAXCARDEXPIRYDATEITM AS ‘TAXCARDEXPIRYDATEITM’,
cr_no_att AS ‘cr_no_att’,
CLD_ATTACHMENT as ‘CLD_ATTACHMENT’
) – table column names to replaced to the same names.

I did not use any aggregate function as it is a straight foward transpose.

Source of Learning