Click here to Skip to main content
15,936,565 members
Home / Discussions / Database
   

Database

 
GeneralRe: Conversion failed in sql Pin
Mycroft Holmes24-Apr-13 12:29
professionalMycroft Holmes24-Apr-13 12:29 
QuestionTunig required for the below SQL. Pin
nirkar jena16-Apr-13 23:42
nirkar jena16-Apr-13 23:42 
AnswerRe: Tunig required for the below SQL. Pin
Mycroft Holmes17-Apr-13 0:45
professionalMycroft Holmes17-Apr-13 0:45 
AnswerRe: Tunig required for the below SQL. Pin
Eddy Vluggen17-Apr-13 5:06
professionalEddy Vluggen17-Apr-13 5:06 
AnswerRe: Tunig required for the below SQL. Pin
Niral Soni22-Apr-13 3:13
Niral Soni22-Apr-13 3:13 
QuestionNeed a query to get data from two tables with specific format Pin
sowvin16-Apr-13 20:41
sowvin16-Apr-13 20:41 
AnswerRe: Need a query to get data from two tables with specific format Pin
Mycroft Holmes16-Apr-13 22:01
professionalMycroft Holmes16-Apr-13 22:01 
AnswerRe: Need a query to get data from two tables with specific format Pin
Niral Soni22-Apr-13 4:57
Niral Soni22-Apr-13 4:57 
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.

SQL
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

QuestionSSIS XML Source (2012) Pin
PIEBALDconsult16-Apr-13 18:26
mvePIEBALDconsult16-Apr-13 18:26 
QuestionHow to insert the same record multiple times in a MS Access 2000 database Pin
sobelhaj12-Apr-13 7:39
sobelhaj12-Apr-13 7:39 
AnswerRe: How to insert the same record multiple times in a MS Access 2000 database Pin
RobCollins12-Apr-13 12:12
RobCollins12-Apr-13 12:12 
AnswerRe: How to insert the same record multiple times in a MS Access 2000 database Pin
PIEBALDconsult13-Apr-13 8:01
mvePIEBALDconsult13-Apr-13 8:01 
GeneralRe: How to insert the same record multiple times in a MS Access 2000 database Pin
sobelhaj13-Apr-13 9:00
sobelhaj13-Apr-13 9:00 
GeneralRe: How to insert the same record multiple times in a MS Access 2000 database Pin
PIEBALDconsult13-Apr-13 11:18
mvePIEBALDconsult13-Apr-13 11:18 
AnswerRe: How to insert the same record multiple times in a MS Access 2000 database Pin
Mycroft Holmes13-Apr-13 13:20
professionalMycroft Holmes13-Apr-13 13:20 
AnswerRe: How to insert the same record multiple times in a MS Access 2000 database Pin
jschell14-Apr-13 7:12
jschell14-Apr-13 7:12 
Questionthe output of select command Pin
sara-setare12-Apr-13 7:13
sara-setare12-Apr-13 7:13 
AnswerRe: the output of select command Pin
RobCollins12-Apr-13 7:17
RobCollins12-Apr-13 7:17 
GeneralRe: the output of select command Pin
sara-setare12-Apr-13 7:28
sara-setare12-Apr-13 7:28 
GeneralRe: the output of select command Pin
RobCollins12-Apr-13 7:59
RobCollins12-Apr-13 7:59 
GeneralRe: the output of select command Pin
sara-setare12-Apr-13 19:44
sara-setare12-Apr-13 19:44 
Questionupdate special row Pin
sara-setare12-Apr-13 1:20
sara-setare12-Apr-13 1:20 
AnswerRe: update special row Pin
Tim Carmichael12-Apr-13 1:35
Tim Carmichael12-Apr-13 1:35 
AnswerRe: update special row Pin
Shanalal Kasim12-Apr-13 3:11
Shanalal Kasim12-Apr-13 3:11 
AnswerRe: update special row Pin
David Mujica12-Apr-13 3:11
David Mujica12-Apr-13 3:11 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.