So you want to switch the rows and columns over?
If so you can do this with an unpivot and pivot!
- Use dense_rank to assign a number to parameters and values
- Then unpivot to convert names and values into rows
- The pivot by the dense rank number so they become columns:
CREATE TABLE TB_PARAM_MST
( PK_TB_COMPANY_CONF_PARAM NUMBER(10,0) NOT NULL ENABLE,
PARAMETER_NAME VARCHAR2(50 CHAR) NOT NULL ENABLE,
CREATED_ON DATE NOT NULL ENABLE,
CREATED_BY NUMBER(10,0) NOT NULL ENABLE,
MODIFIED_ON DATE,
MODIFIED_BY NUMBER(10,0),
REC_STATUS VARCHAR2(2 CHAR) NOT NULL ENABLE
) ;
CREATE TABLE TB_PARAM_DTL
( PK_TB_COMPANY_CONF_REG_DEF NUMBER(10,0) NOT NULL ENABLE,
REGION_SHORT_CODE VARCHAR2(2 CHAR) NOT NULL ENABLE,
FK_TB_COMPANY_CONF_PARAM NUMBER(10,0) NOT NULL ENABLE,
DEFAULT_PARAM_VALUE VARCHAR2(100 CHAR),
CREATED_ON DATE NOT NULL ENABLE,
CREATED_BY NUMBER(10,0) NOT NULL ENABLE,
MODIFIED_ON DATE,
MODIFIED_BY NUMBER(10,0),
REC_STATUS VARCHAR2(2 CHAR) NOT NULL ENABLE
) ;
Insert into TB_PARAM_MST (PK_TB_COMPANY_CONF_PARAM,PARAMETER_NAME,CREATED_ON,CREATED_BY,MODIFIED_ON,MODIFIED_BY,REC_STATUS)
values (44,'CLIENT ACCESS TO CSP',to_date('25-JAN-12','DD-MON-RR'),1,null,null,'AC');
Insert into TB_PARAM_MST (PK_TB_COMPANY_CONF_PARAM,PARAMETER_NAME,CREATED_ON,CREATED_BY,MODIFIED_ON,MODIFIED_BY,REC_STATUS)
values (51,'CLIENT ACCESS TO OCA',to_date('25-JAN-12','DD-MON-RR'),1,null,null,'AC');
Insert into TB_PARAM_MST (PK_TB_COMPANY_CONF_PARAM,PARAMETER_NAME,CREATED_ON,CREATED_BY,MODIFIED_ON,MODIFIED_BY,REC_STATUS)
values (53,'CLIENT ACCESS TO GCMS',to_date('25-JAN-12','DD-MON-RR'),1,null,null,'AC');
Insert into TB_PARAM_DTL (PK_TB_COMPANY_CONF_REG_DEF,REGION_SHORT_CODE,FK_TB_COMPANY_CONF_PARAM,DEFAULT_PARAM_VALUE,CREATED_ON,CREATED_BY,MODIFIED_ON,MODIFIED_BY,REC_STATUS)
values (121,'NA',44,'Y',to_date('17-OCT-16','DD-MON-RR'),1,null,null,'AC');
Insert into TB_PARAM_DTL (PK_TB_COMPANY_CONF_REG_DEF,REGION_SHORT_CODE,FK_TB_COMPANY_CONF_PARAM,DEFAULT_PARAM_VALUE,CREATED_ON,CREATED_BY,MODIFIED_ON,MODIFIED_BY,REC_STATUS)
values (147,'NA',44,'Y',to_date('19-OCT-16','DD-MON-RR'),1,null,null,'AC');
Insert into TB_PARAM_DTL (PK_TB_COMPANY_CONF_REG_DEF,REGION_SHORT_CODE,FK_TB_COMPANY_CONF_PARAM,DEFAULT_PARAM_VALUE,CREATED_ON,CREATED_BY,MODIFIED_ON,MODIFIED_BY,REC_STATUS)
values (161,'NA',51,'Y',to_date('19-OCT-16','DD-MON-RR'),1,null,null,'AC');
Insert into TB_PARAM_DTL (PK_TB_COMPANY_CONF_REG_DEF,REGION_SHORT_CODE,FK_TB_COMPANY_CONF_PARAM,DEFAULT_PARAM_VALUE,CREATED_ON,CREATED_BY,MODIFIED_ON,MODIFIED_BY,REC_STATUS)
values (163,'NA',53,'Login ID',to_date('19-OCT-16','DD-MON-RR'),1,null,null,'AC');
with rws as (
SELECT ccp.parameter_name, ccrd.default_param_value,
dense_rank() over (order by ccp.parameter_name, ccrd.default_param_value) rn
FROM TB_PARAM_MST ccp
, TB_PARAM_DTL ccrd
WHERE ccrd.fk_tb_company_conf_param = ccp.pk_tb_company_conf_param
AND ccp.rec_status = 'AC'
AND ccrd.rec_status = 'AC'
AND ccrd.region_short_code = 'NA'
AND parameter_name in ('CLIENT ACCESS TO CSP','CLIENT ACCESS TO GCMS', 'CLIENT ACCESS TO OCA')
)
select "1", "2", "3"
from rws
unpivot (
val for col in (parameter_name, default_param_value)
)
pivot ( min(val) for rn in (1, 2, 3))
order by col desc;
1 2 3
CLIENT ACCESS TO CSP CLIENT ACCESS TO GCMS CLIENT ACCESS TO OCA
Y Login ID Y
If you want to know more about pivot and unpivot, read:
https://blogs.oracle.com/sql/entry/how_to_convert_rows_to