locked
How to write a query to get out put like RRS feed

  • Question

  • User194385433 posted
    Hi,
    
    I have table data below like this:
    CNAME  ID   Code
    A     1000   CS
    B     1000   CS
    B     1000   DS
    B     1000   ES
    C     1002   CS
    D     1002   CS
    E     1000   CS
    F     1010   CS
    
    CName must Contain Code "CS" and another CName also if Contains "CS" and Other Codes "DS","ES"..etc.
    Then this both Cname should not get in out put.
    
    For Above Sample Table OutPut sholud Contain Like:
    
    CNAME  ID    Code
    C     1002   CS
    D     1002   CS
    F     1010   CS
    
    Thanks,

    Friday, November 20, 2015 2:37 PM

Answers

  • User1644755831 posted

    Hello SureshTalla,

    Please try this.

    DECLARE @TABLE TABLE
    (
     CNAME CHAR(1),
     ID INT,
     Code CHAR(2)
    )
    INSERT @TABLE
    SELECT
    'A',1000,'CS'
    UNION ALL SELECT
    'B',1000,'CS'
    UNION ALL SELECT
    'B',1000,'DS'
    UNION ALL SELECT
    'B',1000,'ES'
    UNION ALL SELECT
    'C',1002,'CS'
    UNION ALL SELECT
    'D',1002,'CS'
    UNION ALL SELECT
    'E',1000,'CS'
    UNION ALL SELECT
    'F',1010,'CS'
    
    ;WIth CTE1 AS
    (
      select 
      ID,
      RANK() OVER (PARTITION BY ID ORDER BY CODE) [Rank]
      FROM 
      @TABLE 
      GROUP BY ID,Code
    ),CTE2 AS
    (
      SELECT ID FROM CTE1 GROUP BY ID HAVING SUM([Rank]) = 1
    )
    select B.* FROM CTE2 A INNER JOIN @TABLE B ON A.ID = B.ID
    
    
    

    Result

    CNAME	ID	Code
    C	1002	CS
    D	1002	CS
    F	1010	CS

    With Regards,

    Krunal Parekh

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 23, 2015 10:06 AM
  • User77042963 posted
    ;with mycte as (
    SELECT CNAME,ID,Code
    ,Sum(CASE WHEN Code='CS' THEN 0 ELSE 1 END) Over(Partition by ID)  cnt 
    FROM @TABLE)
    
    SELECT CNAME,ID,Code FROM mycte
    WHERE cnt=0
     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 23, 2015 3:43 PM

All replies

  • User475983607 posted

    The example does not make sense. There are no records...

    CNAME   ID     Code
    C      1000    CS
    D      1000    CS

    In the original set!

    Friday, November 20, 2015 3:42 PM
  • User194385433 posted

    Thanks for your reply mgebhard,

    sorry i types incorrect output, now i edited correct out put which i need.

    Thanks,

    Monday, November 23, 2015 6:07 AM
  • User1644755831 posted

    Hello SureshTalla,

    Please try this.

    DECLARE @TABLE TABLE
    (
     CNAME CHAR(1),
     ID INT,
     Code CHAR(2)
    )
    INSERT @TABLE
    SELECT
    'A',1000,'CS'
    UNION ALL SELECT
    'B',1000,'CS'
    UNION ALL SELECT
    'B',1000,'DS'
    UNION ALL SELECT
    'B',1000,'ES'
    UNION ALL SELECT
    'C',1002,'CS'
    UNION ALL SELECT
    'D',1002,'CS'
    UNION ALL SELECT
    'E',1000,'CS'
    UNION ALL SELECT
    'F',1010,'CS'
    
    ;WIth CTE1 AS
    (
      select 
      ID,
      RANK() OVER (PARTITION BY ID ORDER BY CODE) [Rank]
      FROM 
      @TABLE 
      GROUP BY ID,Code
    ),CTE2 AS
    (
      SELECT ID FROM CTE1 GROUP BY ID HAVING SUM([Rank]) = 1
    )
    select B.* FROM CTE2 A INNER JOIN @TABLE B ON A.ID = B.ID
    
    
    

    Result

    CNAME	ID	Code
    C	1002	CS
    D	1002	CS
    F	1010	CS

    With Regards,

    Krunal Parekh

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 23, 2015 10:06 AM
  • User77042963 posted
    ;with mycte as (
    SELECT CNAME,ID,Code
    ,Sum(CASE WHEN Code='CS' THEN 0 ELSE 1 END) Over(Partition by ID)  cnt 
    FROM @TABLE)
    
    SELECT CNAME,ID,Code FROM mycte
    WHERE cnt=0
     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 23, 2015 3:43 PM