Show Group Above Details Query

Data in Table :

    ItemCode  ItemName     ItemGroup  
    SFD       Softdrink     NULL
    CCL       Coco Cola     SFD
    FNT       Fanta         SFD
    SPR       Sprite        SFD
    ACL       Alchol            
    TQL       Tequila       ACL
    VDK       Vodka         ACL

When user find "Softdrink" then the result will be:

    ItemCode       ItemName
    SFD            Softdrink     
    CCL            Coca Cola         
    FNT            Fanta                       
    SPR            Sprite

How could I do it?

Thank you.

From stackoverflow

• All softdrinks are in itemgroup sfd. Try select itemcode, itemname from table where itemgroup = 'SFD';

  Mark Hamilton points out:

  Except Softdrink itself doesn't have SFD as its ItemGroup. You'd need to add "or ItemCode = 'SFD'" to your query. I wonder whether the categories can nest further, in which case a recursive CTE would do the trick

  Yeah, either add an 'or' to the where, or go recursive.

  Matt Hamilton : Except Softdrink itself doesn't have SFD as its ItemGroup. You'd need to add "or ItemCode = 'SFD'" to your query. I wonder whether the categories can nest further, in which case a recursive CTE would do the trick.

• Got it Worked with

  select ItemCode, ItemName from Items 
  where itemCode in (select itemcode  from Items where itemname = 'softDrink') 
  or itemGroup in  (select itemcode  from Items where itemname = 'softDrink')
  

  Can this been modified further.

• WITH hier (code, name) AS
          (
          SELECT  ItemCode, ItemName
          FROM    Table
          WHERE   ItemCode = 'SFD'
          UNION ALL
          SELECT  ItemCode, ItemName
          FROM    hier
          JOIN    Table
          ON      ItemGroup = code
          )
  SELECT  *
  FROM    hier
  

  This will select items even with deeper hierarchy level (i. e. you can add Fanta Lemon and Fanta Orange with ItemGroup = FNT, and this query will select them too).