i need a sql query that will display my data with fixed headings in column and row-wise. my table iat has "X" values that i want in column (horizontaly) headings and "Y" value be display in rows (vertical).
if values in table "ia" is found will be display in its particular cell.
for example i have HeadInC1 to HeadInC3 will be shown in columns and HeadInR1 to HeadInR3 be in rows.
value of 33 is in HeadInR2,HeadInC1 and 51 is in HeadInC3.
| Header 1 |
|
result set
|
| data |
|
CREATE TABLE IAT
(
IATCODE VARCHAR2(6 BYTE) NOT NULL,
TITLE VARCHAR2(50 BYTE) NOT NULL,
IATCODEFK VARCHAR2(6 BYTE),
XY VARCHAR2(1 BYTE)
)
ALTER TABLE IAT ADD (PRIMARY KEY(IATCODE);
ALTER TABLE IAT ADD (CONSTRAINT IAT_R01 FOREIGN KEY (IATCODEFK) REFERENCES IAT (IATCODE) ENABLE VALIDATE);
CREATE TABLE IAT
(
IATCODE VARCHAR2(6 BYTE) NOT NULL,
TITLE VARCHAR2(50 BYTE) NOT NULL
)
;
insert into iat (iatcode,title,iatcodefk,xy) values ('000021','HeadInC1','','X');
insert into iat (iatcode,title,iatcodefk,xy) values ('000022','HeadInC2','','X');
insert into iat (iatcode,title,iatcodefk,xy) values ('000023','HeadInC3','','X');
insert into iat (iatcode,title,iatcodefk,xy) values ('000024','HeadInR1','000021','Y');
insert into iat (iatcode,title,iatcodefk,xy) values ('000025','HeadInR2','000022','Y');
insert into iat (iatcode,title,iatcodefk,xy) values ('000026','HeadInR3','000023','Y');
insert into iat (iatcode,title,iatcodefk,xy) values ('000027','HeadInR2','000021','Y');
CREATE TABLE IA
(
IACODE VARCHAR2(20 BYTE) NOT NULL,
IATCODE VARCHAR2(6 BYTE) NOT NULL,
DESCRIPTION_number NUMBER(5,2)
);
insert into ia (iacode,iatcode,description_number) values ('20000','000027',33);
insert into ia (iacode,iatcode,description_number) values ('20001','000026',51);
|