I have a table MEASURES

I want to run a SQL to get the following output

So 2 rows in the table need to become 4 Some indicated an inline view might help

In case you want to try :

CREATE TABLE MEASURES
("M_ID" NUMBER(10,0), 
 "MBK_NAME" VARCHAR2(100 BYTE),
 "MCP_NAME" VARCHAR2(100 BYTE));

Insert into MEASURES (M_ID,MBK_NAME,MCP_NAME) values (10,null,'MCCP');
Insert into MEASURES (M_ID,MBK_NAME,MCP_NAME) values (11,'MCCP','LOOPS');
commit;

UPDATED TO ADD LOGIC as Requested thanks @JR and @DR great. There are more cols in the table m_id, mbk_name and mcp_name are one with logic.

Logic

• Find distinct mbk_names / mcp_names where length >=4 chars
• For each - find the associated 2 latest records in MEASURES (by m_id)
• where there than 2 or more rec take first 2 based on m_id. Output should be that m_id
• where there is only 1 use same id for both and create ABCD and ABCD01 records

Other permuatations for input are

-1-
M_ID   MBK_NAME   MCP_NAME
51     JDKJPP     JDKJPP
57     JDKJPP     JDKJPP
61     JDKJPP     JDKJPP

OUT
51 JDKJ JDKJPP JDKJPP
57 JDKJ01 JDKJPP JDKJPP

-2-
M_ID MBK_NAME MCP_NAME
101 HTTPKHN_GT HTTPKHN_GT

OUT
101 HTTP HTTPKHN_GT HTTPKHN_GT
101 HTTP01 HTTPKHN_GT HTTPKHN_GT

-3-
M_ID MBK_NAME MCP_NAME
15 PIPSTT KOOLXX 25 PIPSTT KOOLXX

OUT
15 PIPS PIPSTT KOOLXX
25 PIPS01 PIPSTT KOOLXX
15 KOOL PIPSTT KOOLXX
25 KOOL01 PIPSTT KOOLXX

Data for Above examples

-- Test 1
TRUNCATE TABLE MEASURES;
Insert into MEASURES (M_ID,MBK_NAME,MCP_NAME) values 
(51,'JDKJPP','JDKJPP');
Insert into MEASURES (M_ID,MBK_NAME,MCP_NAME) values 
(57,'JDKJPP','JDKJPP');
Insert into MEASURES (M_ID,MBK_NAME,MCP_NAME) values 
(61,'JDKJPP','JDKJPP');
commit;

-- Test 2
TRUNCATE TABLE MEASURES;  
Insert into MEASURES (M_ID,MBK_NAME,MCP_NAME) values 
(101,'HTTPKHN_GT','HTTPKHN_GT');  
commit;

-- Test 3
TRUNCATE TABLE MEASURES;
Insert into MEASURES (M_ID,MBK_NAME,MCP_NAME) values 
(15,'PIPSTT','KOOLXX');
Insert into MEASURES (M_ID,MBK_NAME,MCP_NAME) values   
(25,'PIPSTT','KOOLXX');
commit;

CodePudding user response：

MBK_NAME appears to be irrelevant.

Create a table with two rows, containing the text '' and '01'.

JOIN against that table. The Cartesian cross product will give you the desired four rows.

You mentioned (11, MCCP01), but I think you meant (10, MCCP01).

CodePudding user response：

There is no explanation behind the desired output. Like something about why 4 rows and what is the basic logic, is the data structure maybe tree-like, what about orderings, joins... As just something that will give the result, without knowing anything of the above mentioned explanations (and without taking them in consideration) here is the code...

SELECT DISTINCT
    LEVEL "LVL",
    M_ID "M_ID",
    CASE LEVEL 
        WHEN 1 THEN  COALESCE(MBK_NAME, MBK_NAME_2, MBK_NAME_3, MBK_NAME_4) 
        WHEN 2 THEN  COALESCE(MBK_NAME_2, MBK_NAME_3, MBK_NAME_4)
        WHEN 3 THEN  COALESCE(MBK_NAME_3, MBK_NAME_4)
        WHEN 4 THEN  MBK_NAME_4
    END "NAMES"
FROM
    (
        SELECT
            a.M_ID "M_ID",
            CASE WHEN FIRST_VALUE(a.MBK_NAME) OVER(PARTITION BY 1 ORDER BY 1 ROWS BETWEEN 1 PRECEDING And CURRENT ROW) Is Not Null THEN a.MBK_NAME END "MBK_NAME", 
            CASE WHEN a.MBK_NAME Is Not Null THEN a.MBK_NAME || LPAD(ROWNUM - 1, 2, '0') END "MBK_NAME_2",
            COALESCE (SubStr(a.MCP_NAME, 1, 4), SubStr(a.MBK_NAME, 1, 4)) "MBK_NAME_3",
            CASE WHEN a.MBK_NAME Is Not Null THEN COALESCE (SubStr(a.MCP_NAME, 1, 4), SubStr(a.MBK_NAME, 1, 4)) || LPAD(ROWNUM - 1, 2, '0') END "MBK_NAME_4"
        FROM
            MEASURES a
        ORDER BY
            a.M_ID
    )
WHERE (LEVEL = 1 And CASE WHEN MBK_NAME_2 Is Null THEN 'Y' ELSE 'N' END = 'Y') OR 
      ( LEVEL <> 1 And CASE WHEN MBK_NAME_2 Is Null THEN 'Y' ELSE 'N' END = 'N')
CONNECT BY LEVEL <= 4
ORDER BY LEVEL, M_ID

...resulting as