将行转换为列Oracle [英] Convert rows into columns Oracle
本文介绍了将行转换为列Oracle的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
这个问题已经被问了好几次，并被回答了很多次，但这是不同的，因为:
This has been asked several times and has been answered as many times, but this is different because:
- 这是在Oracle 11g中-我看到的其他示例是在具有StUFF函数的SQL Server中
- 这需要联接2个表以获取信息
让我解释一下
此表包含字段名称& File_ID
This is the table that holds field names & File_ID
CREATE TABLE RESPONSE_METADATA
(
FILE_ID NUMBER(10) NOT NULL,
FIELD_NUMBER NUMBER(4) NOT NULL,
FIELD_NAME VARCHAR2(50 BYTE) NOT NULL,
FIELD_TYPE VARCHAR2(50 BYTE) NOT NULL
);
这是一个表，其中包含每个文件的记录，但记录是分开的.
This is a table that holds the records of each file but records are separated.
CREATE TABLE RESPONSE_DETAILS
(
FILE_ID NUMBER(10),
REC_FLD_1 VARCHAR2(300 BYTE),
REC_FLD_2 VARCHAR2(300 BYTE),
REC_FLD_3 VARCHAR2(300 BYTE),
REC_FLD_4 VARCHAR2(300 BYTE),
REC_FLD_5 VARCHAR2(300 BYTE),
REC_FLD_6 VARCHAR2(300 BYTE),
REC_FLD_7 VARCHAR2(300 BYTE),
REC_FLD_8 VARCHAR2(300 BYTE),
REC_FLD_9 VARCHAR2(300 BYTE),
REC_FLD_10 VARCHAR2(300 BYTE),
REC_FLD_11 VARCHAR2(300 BYTE),
REC_FLD_12 VARCHAR2(300 BYTE),
REC_FLD_13 VARCHAR2(300 BYTE),
REC_FLD_14 VARCHAR2(300 BYTE),
REC_FLD_15 VARCHAR2(300 BYTE),
REC_FLD_16 VARCHAR2(300 BYTE),
REC_FLD_17 VARCHAR2(300 BYTE),
REC_FLD_18 VARCHAR2(300 BYTE),
REC_FLD_19 VARCHAR2(300 BYTE),
REC_FLD_20 VARCHAR2(300 BYTE)
);
插入数据
INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (1,1,'REC_ID','NUMBER');
INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (1,2,'EMAIL','VARCHAR2');
INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (1,3,'CAMP','VARCHAR2');
INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (1,4,'ACTION','VARCHAR2');
INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (1,5,'DESC','VARCHAR2');
INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (1,6,'TIME','VARCHAR2');
INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (1,7,'CHANNEL','NUMBER');
INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (1,8,'STATUS','VARCHAR2');
INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (1,9,'DI_CODE','VARCHAR2');
INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (1,10,'CUSTOM','VARCHAR2');
INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (2,1,'REC_ID','NUMBER');
INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (2,2,'EMAIL','VARCHAR2');
INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (2,3,'CAMP','VARCHAR2');
INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (2,4,'ACTION','VARCHAR2');
INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (2,5,'DESC','VARCHAR2');
INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (3,1,'REC_ID','NUMBER');
INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (3,2,'EMAIL','VARCHAR2');
INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (3,3,'CAMP','VARCHAR2');
INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (4,1,'REC_ID','NUMBER');
INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (4,2,'ORGN','VARCHAR2');
INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (4,3,'LOGOS','VARCHAR2');
INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (4,4,'ACTN','VARCHAR2');
INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (4,5,'BLANK1','VARCHAR2');
INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (4,6,'ALERT_ID','VARCHAR2');
INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (4,7,'DEVNUM','NUMBER');
INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (4,8,'RES_CODE','VARCHAR2');
INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (4,9,'STRAT_ID','VARCHAR2');
INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (4,10,'COMP_FLAG','VARCHAR2');
INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (4,11,'AUDLOG','VARCHAR2');
INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (4,12,'STARTTIME','VARCHAR2');
INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (4,13,'DURATION','VARCHAR2');
INSERT INTO RESPONSE_DETAILS (FILE_ID, REC_FLD_1, REC_FLD_2, REC_FLD_3, REC_FLD_4, REC_FLD_5, REC_FLD_6, REC_FLD_7, REC_FLD_8, REC_FLD_9, REC_FLD_10, REC_FLD_11, REC_FLD_12, REC_FLD_13, REC_FLD_14, REC_FLD_15, REC_FLD_16, REC_FLD_17, REC_FLD_18, REC_FLD_19, REC_FLD_20)VALUES (8,'67537','Line','','81433','1','15062012','DELIVERED','67537','','','','','','','','','','','','');
INSERT INTO RESPONSE_DETAILS (FILE_ID, REC_FLD_1, REC_FLD_2, REC_FLD_3, REC_FLD_4, REC_FLD_5, REC_FLD_6, REC_FLD_7, REC_FLD_8, REC_FLD_9, REC_FLD_10, REC_FLD_11, REC_FLD_12, REC_FLD_13, REC_FLD_14, REC_FLD_15, REC_FLD_16, REC_FLD_17, REC_FLD_18, REC_FLD_19, REC_FLD_20)VALUES (8,'67551','Line','','81430','1','15062012','DELIVERED','67551','','','','','','','','','','','','');
INSERT INTO RESPONSE_DETAILS (FILE_ID, REC_FLD_1, REC_FLD_2, REC_FLD_3, REC_FLD_4, REC_FLD_5, REC_FLD_6, REC_FLD_7, REC_FLD_8, REC_FLD_9, REC_FLD_10, REC_FLD_11, REC_FLD_12, REC_FLD_13, REC_FLD_14, REC_FLD_15, REC_FLD_16, REC_FLD_17, REC_FLD_18, REC_FLD_19, REC_FLD_20)VALUES (8,'67579','Line','','81403','1','15062012','DELIVERED','67579','','','','','','','','','','','','');
INSERT INTO RESPONSE_DETAILS (FILE_ID, REC_FLD_1, REC_FLD_2, REC_FLD_3, REC_FLD_4, REC_FLD_5, REC_FLD_6, REC_FLD_7, REC_FLD_8, REC_FLD_9, REC_FLD_10, REC_FLD_11, REC_FLD_12, REC_FLD_13, REC_FLD_14, REC_FLD_15, REC_FLD_16, REC_FLD_17, REC_FLD_18, REC_FLD_19, REC_FLD_20)VALUES (8,'67593','Line','','81452','1','15062012','DELIVERED','67593','','','','','','','','','','','','');
INSERT INTO RESPONSE_DETAILS (FILE_ID, REC_FLD_1, REC_FLD_2, REC_FLD_3, REC_FLD_4, REC_FLD_5, REC_FLD_6, REC_FLD_7, REC_FLD_8, REC_FLD_9, REC_FLD_10, REC_FLD_11, REC_FLD_12, REC_FLD_13, REC_FLD_14, REC_FLD_15, REC_FLD_16, REC_FLD_17, REC_FLD_18, REC_FLD_19, REC_FLD_20)VALUES (8,'67607','Line','','81453','1','15062012','DELIVERED','67607','','','','','','','','','','','','');
INSERT INTO RESPONSE_DETAILS (FILE_ID, REC_FLD_1, REC_FLD_2, REC_FLD_3, REC_FLD_4, REC_FLD_5, REC_FLD_6, REC_FLD_7, REC_FLD_8, REC_FLD_9, REC_FLD_10, REC_FLD_11, REC_FLD_12, REC_FLD_13, REC_FLD_14, REC_FLD_15, REC_FLD_16, REC_FLD_17, REC_FLD_18, REC_FLD_19, REC_FLD_20)VALUES (4,'28072','16','1','189325','','ins/code.com/20120621','6551','WID ','VOLN ','N',';B10100','41061.6740856481','14','','','','','','','');
INSERT INTO RESPONSE_DETAILS (FILE_ID, REC_FLD_1, REC_FLD_2, REC_FLD_3, REC_FLD_4, REC_FLD_5, REC_FLD_6, REC_FLD_7, REC_FLD_8, REC_FLD_9, REC_FLD_10, REC_FLD_11, REC_FLD_12, REC_FLD_13, REC_FLD_14, REC_FLD_15, REC_FLD_16, REC_FLD_17, REC_FLD_18, REC_FLD_19, REC_FLD_20)VALUES (4,'28072','16','1','189325002','','ins/code.com/20120622','6551','CON ','VOLN ','N',';B10100','41061.6978240741','124','','','','','','','');
INSERT INTO RESPONSE_DETAILS (FILE_ID, REC_FLD_1, REC_FLD_2, REC_FLD_3, REC_FLD_4, REC_FLD_5, REC_FLD_6, REC_FLD_7, REC_FLD_8, REC_FLD_9, REC_FLD_10, REC_FLD_11, REC_FLD_12, REC_FLD_13, REC_FLD_14, REC_FLD_15, REC_FLD_16, REC_FLD_17, REC_FLD_18, REC_FLD_19, REC_FLD_20)VALUES (4,'28075','15','30','443433001','','ins/code.com/20120623','5908','AMP ','VOLN ','N',';B10100','41061.6737268519','13','','','','','','','');
INSERT INTO RESPONSE_DETAILS (FILE_ID, REC_FLD_1, REC_FLD_2, REC_FLD_3, REC_FLD_4, REC_FLD_5, REC_FLD_6, REC_FLD_7, REC_FLD_8, REC_FLD_9, REC_FLD_10, REC_FLD_11, REC_FLD_12, REC_FLD_13, REC_FLD_14, REC_FLD_15, REC_FLD_16, REC_FLD_17, REC_FLD_18, REC_FLD_19, REC_FLD_20)VALUES (4,'28075','15','30','4443433','','ins/code.com/20120624','5908','CON ','VOLN ','N',';B10100','41061.6953240741','113','','','','','','','');
INSERT INTO RESPONSE_DETAILS (FILE_ID, REC_FLD_1, REC_FLD_2, REC_FLD_3, REC_FLD_4, REC_FLD_5, REC_FLD_6, REC_FLD_7, REC_FLD_8, REC_FLD_9, REC_FLD_10, REC_FLD_11, REC_FLD_12, REC_FLD_13, REC_FLD_14, REC_FLD_15, REC_FLD_16, REC_FLD_17, REC_FLD_18, REC_FLD_19, REC_FLD_20)VALUES (4,'28078','11','1','2576141','','ins/code.com/20120625','6681','CON ','VOLN ','N',';B10100','41061.6764236111','81','','','','','','','');
现在问题出在
我希望以这种方式查看数据
I want data to be seen this way
FILE_ID REC_ID EMAIL CAMP ACTION DESC TIME CHANNEL STATUS DI_CODE CUSTOM
1 67537 Line 81433 1 15062012 DELIVERED 67537
1 67551 Line 81430 1 15062012 DELIVERED 67551
1 67579 Line 81403 1 15062012 DELIVERED 67579
1 67593 Line 81452 1 15062012 DELIVERED 67593
1 67607 Line 81453 1 15062012 DELIVERED 67607
FILE_ID REC_ID ORGN LOGOS ACTN BLANK1 ALERT_ID DEVNUM RES_CODE STRAT_ID COMP_FLAG AUDLOG STARTTIME DURATION
4 28072 16 1 189325 ins/code.com/20120621 6551 WID VOLN N ;B10100 1/06/2012 16:10 14
4 28072 16 1 1893250 ins/code.com/20120622 6551 CON VOLN N ;B10100 1/06/2012 16:44 124
4 28075 15 30 4434330 ins/code.com/20120623 5908 AMP VOLN N ;B10100 1/06/2012 16:10 13
4 28075 15 30 4443433 ins/code.com/20120624 5908 CON VOLN N ;B10100 1/06/2012 16:41 113
4 28078 11 1 2576141 ins/code.com/20120625 6681 CON VOLN N ;B10100 1/06/2012 16:14 81
工作方式是METADATA表中的每个File_ID都有FIELD_NUMBER.这些Filed_Numbers是详细信息"表中的列
The way it needs to work is every File_ID in METADATA Table has FIELD_NUMBER. These Filed_Numbers are columns in Details table
我已经尝试过在Oracle中使用XML PIVOT，但由于这些列将创建多个节点，因此无法正常工作.
I've tried XML PIVOT in Oracle that does not work as there are multiple nodes these columns will create.
我已经尝试过只做一些事情-不起作用.这需要动态-File_ID可以具有不超过30个的任意数量的字段.
I've tried just pivot things - does not work. THIS NEEDS TO BE DYNAMIC - THE File_ID can have any number of Fields not more than 30 though.
这需要是存储的proc或查询-因为我没有建立ETL的权限.
And this needs to be a stored proc or query - as I do not have permission to be build ETL.
推荐答案
尝试使用此命令生成脚本:
try this to generate a script:
select 'select '||FILE_ID||' FILE_ID,'||
ltrim(sys_connect_by_path('REC_FLD_'||FIELD_NUMBER||' "'||FIELD_NAME||'"',','),',')||
' from RESPONSE_DETAILS where FILE_ID=' ||FILE_ID||';'
from (select t.*,count(*) over (partition by FILE_ID) cnt from RESPONSE_METADATA t)
where cnt=FIELD_NUMBER start with FIELD_NUMBER=1
connect by prior FILE_ID=FILE_ID and prior FIELD_NUMBER=FIELD_NUMBER-1
添加双引号，因为某些列名称例如保留为"DESC"
Added double quotes because some column names are reserved "DESC" for example
这篇关于将行转换为列Oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
