不确定标题是否有意义，但这是我的问题以及如何到达当前位置.
Not sure if the title makes sense but here is my problem and how I got to where I am.
背景:我在一对多设置中有2个表. many表可以具有与该表关联的任何数量的记录，但是具有一个附加列，该列可以将多条记录标识为介于1到7之间的任何值.我用7个CTE来收集"每个1-7组的所有行，然后将它们连接到一个表上，使我到达示例数据的地步.
Background: I have 2 tables in a one-to-many setup. The many table can have any number of records that tie back to the one but has an additional column that can identify the many record as anything between a value of 1-7. I "gathered" all the rows for each group 1-7 with 7 CTE's and then joined them together to the one table getting me to the point of my sample data.
如果单个列中的所有行都为空，那么我想显示一个*，否则按GROUPID/GROUPNAME在每个列中显示所有不同的值.我有些骇人听闻，并以一种告诫方式大幅度削减了获取自己想要的东西的方式.如果可能的话，我想在GROUPID/GROUPNAME中分别对每一列进行排序，以使NULL不在数据中间.
If all of the rows in a single column are null then I want to display an *, otherwise display all distinct values within each column per GROUPID/GROUPNAME. I've somewhat hack and slashed my way to getting what I want with one caveat. If possible I would like to order each column individually within the GROUPID/GROUPNAME so that NULLs aren't in the middle of the data.
这是我的代码，使我陷入了困境:
Here is my code that got me to the point I am stuck at:
WITH SAMPLEDATA (CYCLEID,GROUPID,GROUPNAME,COL1,COL2,COL3,COL4,COL5,COL6,COL7) AS ( SELECT 1,7669,'000000261','GAS',NULL,NULL,NULL,'1',NULL,'00' FROM DUAL UNION ALL SELECT 2,7669,'000000261','GAS',NULL,NULL,NULL,'1',NULL,'000000261' FROM DUAL UNION ALL SELECT 3,7669,'000000261','GAS',NULL,NULL,NULL,'Chester',NULL,'00' FROM DUAL UNION ALL SELECT 4,7669,'000000261','GAS',NULL,NULL,NULL,'Chester',NULL,'000000261' FROM DUAL UNION ALL SELECT 5,7669,'000000261','GFG',NULL,NULL,NULL,'1',NULL,'00' FROM DUAL UNION ALL SELECT 6,7669,'000000261','GFG',NULL,NULL,NULL,'1',NULL,'000000261' FROM DUAL UNION ALL SELECT 7,7669,'000000261','GFG',NULL,NULL,NULL,'Chester',NULL,'00' FROM DUAL UNION ALL SELECT 8,7669,'000000261','GFG',NULL,NULL,NULL,'Chester',NULL,'000000261' FROM DUAL UNION ALL SELECT 9,7669,'000000261','GKE',NULL,NULL,NULL,'1',NULL,'00' FROM DUAL UNION ALL SELECT 10,7669,'000000261','GKE',NULL,NULL,NULL,'1',NULL,'000000261' FROM DUAL UNION ALL SELECT 11,7669,'000000261','GKE',NULL,NULL,NULL,'Chester',NULL,'00' FROM DUAL UNION ALL SELECT 12,7669,'000000261','GKE',NULL,NULL,NULL,'Chester',NULL,'000000261' FROM DUAL ) , GROUPSPROCESSED AS ( SELECT GROUPID, GROUPNAME, CASE WHEN cycleid = 1 THEN NVL(COL1,'*') WHEN COL1 = FIRST_VALUE(COL1) OVER (PARTITION BY GROUPNAME ORDER BY GROUPNAME, COL1) THEN NULL WHEN COL1 = LAG(COL1) OVER (PARTITION BY GROUPNAME ORDER BY GROUPNAME, COL1) THEN NULL ELSE COL1 END AS "COL1", CASE WHEN cycleid = 1 THEN NVL(COL2,'*') WHEN COL2 = FIRST_VALUE(COL2) OVER (PARTITION BY GROUPNAME ORDER BY GROUPNAME, COL2) THEN NULL WHEN COL2 = LAG(COL2) OVER (PARTITION BY GROUPNAME ORDER BY GROUPNAME, COL2) THEN NULL ELSE COL2 END AS "COL2", CASE WHEN cycleid = 1 THEN NVL(COL3,'*') WHEN COL3 = FIRST_VALUE(COL3) OVER (PARTITION BY GROUPNAME ORDER BY GROUPNAME, COL3) THEN NULL WHEN COL3 = LAG(COL3) OVER (PARTITION BY GROUPNAME ORDER BY GROUPNAME, COL3) THEN NULL ELSE COL3 END AS "COL3", CASE WHEN cycleid = 1 THEN NVL(COL4,'*') WHEN COL4 = FIRST_VALUE(COL4) OVER (PARTITION BY GROUPNAME ORDER BY GROUPNAME, COL4) THEN NULL WHEN COL4 = LAG(COL4) OVER (PARTITION BY GROUPNAME ORDER BY GROUPNAME, COL4) THEN NULL ELSE COL4 END AS "COL4", CASE WHEN cycleid = 1 THEN NVL(COL5,'*') WHEN COL5 = FIRST_VALUE(COL5) OVER (PARTITION BY GROUPNAME ORDER BY GROUPNAME, COL5) THEN NULL WHEN COL5 = LAG(COL5) OVER (PARTITION BY GROUPNAME ORDER BY GROUPNAME, COL5) THEN NULL ELSE COL5 END AS "COL5", CASE WHEN cycleid = 1 THEN NVL(COL6,'*') WHEN COL6 = FIRST_VALUE(COL6) OVER (PARTITION BY GROUPNAME ORDER BY GROUPNAME, COL6) THEN NULL WHEN COL6 = LAG(COL6) OVER (PARTITION BY GROUPNAME ORDER BY GROUPNAME, COL6) THEN NULL ELSE COL6 END AS "COL6", CASE WHEN cycleid = 1 THEN NVL(COL7,'*') WHEN COL7 = FIRST_VALUE(COL7) OVER (PARTITION BY GROUPNAME ORDER BY GROUPNAME, COL7) THEN NULL WHEN COL7 = LAG(COL7) OVER (PARTITION BY GROUPNAME ORDER BY GROUPNAME, COL7) THEN NULL ELSE COL7 END AS "COL7" FROM SAMPLEDATA) SELECT * FROM GROUPSPROCESSED WHERE COALESCE (COL1,COL2,COL3,COL4,COL5,COL6,COL7) IS NOT NULL ORDER BY 1,2,3,4,5,6,7,8,9电流输出:
所需的输出:
编辑 这是代码，可以在执行任何操作之前从我的示例中导入确切的原始数据.从技术上讲，我需要将3个表捆绑在一起以破坏上面的输出.
EDIT Here is code to import the exact raw data from my example before any manipulation. It's technically 3 tables that I need to tie together to derrive the output above.
GROUPPROFILE在上面的输出中保存了字段1-2中的ID和NAME.
The GROUPPROFILE holds the ID and NAME from fields 1-2 in my output above.
COLVALUES根据COLLEVELID为1-7保留输出中其余字段的数据(NAME)
The COLVALUES holds the data (NAME) for the remainder of the fields in the output based on the COLLEVELID being 1-7
GROUPCOLMM表仅用于连接GROUPPROFILE.GROUPPROFILEID> COLVALUES.COLVALUEID
The GROUPCOLMM table is just used to connect GROUPPROFILE.GROUPPROFILEID > COLVALUES.COLVALUEID
CREATE TABLE GROUPPROFILE (GROUPPROFILEID NUMBER(12), SHORTNM NVARCHAR2(30)) REM INSERTING into GROUPPROFILE Insert into GROUPPROFILE (GROUPPROFILEID,SHORTNM) values (7669,'000000261'); CREATE TABLE COLVALUES (COLVALUEID NUMBER(12), NAME NVARCHAR2(50), COLLEVELID NUMBER(12)) REM INSERTING into COLVALUES Insert into COLVALUES (COLVALUEID,NAME,COLLEVELID) values (18901,'GFG',1); Insert into COLVALUES (COLVALUEID,NAME,COLLEVELID) values (106,'GKE',1); Insert into COLVALUES (COLVALUEID,NAME,COLLEVELID) values (1351,'GAS',1); Insert into COLVALUES (COLVALUEID,NAME,COLLEVELID) values (423,'0',4); Insert into COLVALUES (COLVALUEID,NAME,COLLEVELID) values (1,'1',5); Insert into COLVALUES (COLVALUEID,NAME,COLLEVELID) values (18514,'Chester',5); CREATE TABLE GROUPCOLMM (GROUPPROFILEID NUMBER(12), COLVALUEID NUMBER(12)) REM INSERTING into GROUPCOLMM Insert into GROUPCOLMM (GROUPPROFILEID,COLVALUEID) values (7669,1); Insert into GROUPCOLMM (GROUPPROFILEID,COLVALUEID) values (7669,3); Insert into GROUPCOLMM (GROUPPROFILEID,COLVALUEID) values (7669,106); Insert into GROUPCOLMM (GROUPPROFILEID,COLVALUEID) values (7669,1351); Insert into GROUPCOLMM (GROUPPROFILEID,COLVALUEID) values (7669,16503); Insert into GROUPCOLMM (GROUPPROFILEID,COLVALUEID) values (7669,18514); Insert into GROUPCOLMM (GROUPPROFILEID,COLVALUEID) values (7669,18901);推荐答案
您无法从发布的原始"数据中获取示例数据.我从子查询ORIGINALDATA中的样本数据中对原始数据进行了反向工程，然后提出了以下解决方案:
You can't get your sample data from the "raw" data you posted. I reverse engineered raw data from your sample data in the subquery ORIGINALDATA, then came up with this solution:
WITH SAMPLEDATA (CYCLEID,GROUPID,GROUPNAME,COL1,COL2,COL3,COL4,COL5,COL6,COL7) AS ( SELECT 1,7669,'000000261','GAS',NULL,NULL,NULL,'1',NULL,'00' FROM DUAL UNION ALL SELECT 2,7669,'000000261','GAS',NULL,NULL,NULL,'1',NULL,'000000261' FROM DUAL UNION ALL SELECT 3,7669,'000000261','GAS',NULL,NULL,NULL,'Chester',NULL,'00' FROM DUAL UNION ALL SELECT 4,7669,'000000261','GAS',NULL,NULL,NULL,'Chester',NULL,'000000261' FROM DUAL UNION ALL SELECT 5,7669,'000000261','GFG',NULL,NULL,NULL,'1',NULL,'00' FROM DUAL UNION ALL SELECT 6,7669,'000000261','GFG',NULL,NULL,NULL,'1',NULL,'000000261' FROM DUAL UNION ALL SELECT 7,7669,'000000261','GFG',NULL,NULL,NULL,'Chester',NULL,'00' FROM DUAL UNION ALL SELECT 8,7669,'000000261','GFG',NULL,NULL,NULL,'Chester',NULL,'000000261' FROM DUAL UNION ALL SELECT 9,7669,'000000261','GKE',NULL,NULL,NULL,'1',NULL,'00' FROM DUAL UNION ALL SELECT 10,7669,'000000261','GKE',NULL,NULL,NULL,'1',NULL,'000000261' FROM DUAL UNION ALL SELECT 11,7669,'000000261','GKE',NULL,NULL,NULL,'Chester',NULL,'00' FROM DUAL UNION ALL SELECT 12,7669,'000000261','GKE',NULL,NULL,NULL,'Chester',NULL,'000000261' FROM DUAL ) , originaldata as ( select distinct groupid, groupname, col, val from sampledata unpivot (val for col in (COL1 as 1,COL2 as 2,COL3 as 3,COL4 as 4,COL5 as 5,COL6 as 6,COL7 as 7)) ) select GROUPID, GROUPNAME, case when rn = 1 and col1 is null then '*' else col1 end col1, case when rn = 1 and col2 is null then '*' else col2 end col2, case when rn = 1 and col3 is null then '*' else col3 end col3, case when rn = 1 and col4 is null then '*' else col4 end col4, case when rn = 1 and col5 is null then '*' else col5 end col5, case when rn = 1 and col6 is null then '*' else col6 end col6, case when rn = 1 and col7 is null then '*' else col7 end col7 from ( select o.*, row_number() over(partition by groupid, groupname, col order by val) rn from originaldata o ) pivot( max(val) for col in (1 as COL1,2 as COL2,3 as COL3,4 as COL4,5 as COL5,6 as COL6,7 as COL7) ) order by groupid, groupname, rn; GROUPID GROUPNAME COL1 COL2 COL3 COL4 COL5 COL6 COL7 ---------- --------- --------- --------- --------- --------- --------- --------- --------- 7669 000000261 GAS * * * 1 * 00 7669 000000261 GFG Chester 000000261 7669 000000261 GKE最诚挚的问候， 炖阿什顿
Best regards, Stew Ashton
更多推荐
在分区内上移非空值
发布评论