3 Replies Latest reply: Nov 23, 2012 12:39 AM by Manik RSS

    Help required in writing an sql querry

    962348
      Hi guys,

      Could any one hlep me out in writing an sql querry for the below requirement.

      the requirement is: for a perticular ID if we have different names then we need to get it out as multiple else the name itselef. I have given the sample input and the expected output.

      ID Name
      1 A
      1 B
      2 F
      2 F
      3 C
      4 D
      4 E

      Out put should look like
      ID Name
      1 Multiple
      2 F
      3 C
      4 Multiple


      Thanks in advance
      Shylender.

      Edited by: 959345 on Nov 22, 2012 10:13 PM
        • 1. Re: Help required in writing an sql querry
          ranit B
          Check this...
          with xx as(
              select 1 id,'A' n from dual union all
              select 1 id,'B' n from dual union all
              select 2 id,'F' n from dual union all
              select 2 id,'F' n from dual union all
              select 3 id,'C' n from dual union all
              select 4 id,'D' n from dual union all
              select 4 id,'E' n from dual 
          )
          SELECT distinct id,
                 CASE WHEN (COUNT (*) OVER (PARTITION BY id)) > 1 THEN 'multiple' 
                  ELSE n
                 END CASE
            FROM (SELECT DISTINCT id, n FROM xx)
            order by id;    
          gives
          1     multiple
          2     F
          3     C
          4     multiple
          • 2. Re: Help required in writing an sql querry
            sukhijank
            Try this
            WITH tbl AS (SELECT 1 id, 'A' name FROM DUAL
                         UNION
                         SELECT 1 id, 'B' name FROM DUAL
                         UNION
                         SELECT 2 id, 'F' name FROM DUAL
                         UNION
                         SELECT 2 id, 'F' name FROM DUAL
                         UNION
                         SELECT 3 id, 'C' name FROM DUAL
                         UNION
                         SELECT 4 id, 'D' name FROM DUAL
                         UNION
                         SELECT 4 id, 'E' name FROM DUAL)
            SELECT   id, DECODE (COUNT (DISTINCT name), 1, MAX (name), 'Multiple') name
            FROM     tbl
            GROUP BY id
            • 3. Re: Help required in writing an sql querry
              Manik
              May be this:
              WITH t AS
                      (SELECT 1 id, 'A' str FROM DUAL
                       UNION ALL
                       SELECT 1, 'B' FROM DUAL
                       UNION ALL
                       SELECT 2, 'F' FROM DUAL
                       UNION ALL
                       SELECT 2, 'F' FROM DUAL
                       UNION ALL
                       SELECT 3, 'C' FROM DUAL
                       UNION ALL
                       SELECT 4, 'D' FROM DUAL
                       UNION ALL
                       SELECT 4, 'E' FROM DUAL),
                   t1 AS
                      (SELECT id,
                              CASE
                                 WHEN COUNT (DISTINCT str) OVER (PARTITION BY id) > 1 THEN
                                    'MULTIPLE'
                                 ELSE
                                    str
                              END
                                 str
                         FROM t)
                SELECT id, str
                  FROM t1
              GROUP BY id, str
              ORDER BY 1;
              Cheers,
              Manik.