Need a query to get data from two tables with specific format
-
Hello, Thanks in advance. Table-1 startdate MSNUM 12/12/2012 1,2,3 12/10/2010 4 12/9/2009 5,6 12/13/2014 10/3/2003 Table-2 ID DESC 1 ONE 2 TWO 3 THREE 4 FOUR 5 FIVE Expected OutPut Result MSGNUM DESC 1,2,3 ONE,TWO,THREE 4 FOUR 5,6 FIVE,SIX Note: Table-1 contains 7690 records and table-2 contains 160 fixed rows Please let me know how can get the above output. I am trying using cursors still didn't found any solution. If anyone already worked on this can save my time. best Regards, Kumar
-
Hello, Thanks in advance. Table-1 startdate MSNUM 12/12/2012 1,2,3 12/10/2010 4 12/9/2009 5,6 12/13/2014 10/3/2003 Table-2 ID DESC 1 ONE 2 TWO 3 THREE 4 FOUR 5 FIVE Expected OutPut Result MSGNUM DESC 1,2,3 ONE,TWO,THREE 4 FOUR 5,6 FIVE,SIX Note: Table-1 contains 7690 records and table-2 contains 160 fixed rows Please let me know how can get the above output. I am trying using cursors still didn't found any solution. If anyone already worked on this can save my time. best Regards, Kumar
Because you have stored you MSNUM as a comma separated string (idiot) you are going to have to split the string. For this you need a split function that will return a table with the date and 1 ID per row. You then use this table to join you 2 existing tables. This is the direct result of crappy design where someone is too bloody lazy to create the additional table to store the Date/MSNUM as discreet records. There are plenty of examples around[^]
Never underestimate the power of human stupidity RAH
-
Hello, Thanks in advance. Table-1 startdate MSNUM 12/12/2012 1,2,3 12/10/2010 4 12/9/2009 5,6 12/13/2014 10/3/2003 Table-2 ID DESC 1 ONE 2 TWO 3 THREE 4 FOUR 5 FIVE Expected OutPut Result MSGNUM DESC 1,2,3 ONE,TWO,THREE 4 FOUR 5,6 FIVE,SIX Note: Table-1 contains 7690 records and table-2 contains 160 fixed rows Please let me know how can get the above output. I am trying using cursors still didn't found any solution. If anyone already worked on this can save my time. best Regards, Kumar
I am not sure what database you are using, but below is the query which resolves your problem and that works well in Oracle 11g.
SELECT MSGNUM, LISTAGG(DESCR, ',') WITHIN GROUP (ORDER BY LVL) AS MSGDESCR
FROM (SELECT DISTINCT MSGNUM, LEVEL LVL
,SUBSTR(NVL2(MSGNUM, MSGNUM || ',', NULL)
,(CASE WHEN LEVEL > 1 THEN
INSTR(NVL2(MSGNUM, MSGNUM || ',', NULL), ',', 1, LEVEL - 1) + 1
ELSE LEVEL END)
,INSTR(NVL2(MSGNUM, MSGNUM || ',', NULL), ',', 1, LEVEL)
- (CASE WHEN LEVEL > 1 THEN
INSTR(NVL2(MSGNUM, MSGNUM || ',', NULL), ',', 1, LEVEL - 1) + 1
ELSE LEVEL END)
) MSGNUM_TO_ID
FROM
-- REPLACE FOLLOWING BLOCK WITH YOUR FIRST TABLE
(SELECT SYSDATE - 1 STARTDATE, '1,2,3' MSGNUM FROM DUAL UNION
SELECT SYSDATE - 2 STARTDATE, '4' MSGNUM FROM DUAL UNION
SELECT SYSDATE - 3 STARTDATE, '5,6' MSGNUM FROM DUAL UNION
SELECT SYSDATE - 4 STARTDATE, '' MSGNUM FROM DUAL UNION
SELECT SYSDATE - 5 STARTDATE, '4,2,5' MSGNUM FROM DUAL UNION
SELECT SYSDATE - 6 STARTDATE, '' MSGNUM FROM DUAL)
CONNECT BY INSTR(NVL2(MSGNUM, MSGNUM || ',', NULL), ',', 1, LEVEL) != 0
) T1
-- REPLACE FOLLOWING BLOCK WITH YOUR SECOND TABLE
,(SELECT 1 ID, 'ONE' DESCR FROM DUAL UNION
SELECT 2 ID, 'TWO' DESCR FROM DUAL UNION
SELECT 3 ID, 'THREE' DESCR FROM DUAL UNION
SELECT 4 ID, 'FOUR' DESCR FROM DUAL UNION
SELECT 5 ID, 'FIVE' DESCR FROM DUAL UNION
SELECT 6 ID, 'SIX' DESCR FROM DUAL) T2
WHERE T1.MSGNUM_TO_ID = T2.ID
GROUP BY MSGNUM
;Thanks & Regards, Niral Soni