Need help with a query

RadhaKrishnaKiJaya
RadhaKrishnaKiJaya used Ask the Experts™
on
Hi Experts,

I want to write a query to group the data by groupID.

GroupID      GroupName      MenuItemID      MenuItemName
 8      Cheese      25      Cheese Pizza (VEG)
 8      Cheese      12      Bean & Cheese Quesadilla (GF & VEG)
 8      Cheese      13      Bean & DF Cheese Quesadilla (GF, VEG, & V)
 8      Cheese      26      Cheese Quesadilla (GF & VEG)
 8      Cheese      28      Cheese Quesadilla w/ DF Cheese (GF, VEG, & V)
 8      Cheese      27      Cheese Quesadilla (VEG)
 8      Cheese      121      Vegetable Quesadilla (VEG)
 8      Cheese      122      Vegetable Quesadilla w/ DF cheese (VEG & V)
 18      Grilled Cheese      64      Grilled Cheese on GF Bread (GF & VEG)
 18      Grilled Cheese      65      Grilled Cheese w/ DF Cheese (VEG & V)
 18      Grilled Cheese      63      Grilled Cheese (VEG)

 I want like this

 Cheese
     Cheese Pizza (VEG)
     Bean & Cheese Quesadilla (GF & VEG)
     ...
     ...
 Grilled Cheese
     Grilled Cheese on GF Bread (GF & VEG)
     Grilled Cheese w/ DF Cheese (VEG & V)

Thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Patrick Matthews
Top Expert 2010

Commented:
What you really need there is a report to display the data the way you want.  Any of the major report packages would make this a fairly trivial exercise if you already have a query delivering the data as above.
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
SQL does not provide features for pretty presentation

For example, Headings/Sub-Headings and indenting are things SQL does not do (or at least not do well). With all of that said this may help: 
declare @YourTable table
    ([GroupID] int, [GroupName] varchar(14), [MenuItemID] int, [MenuItemName] varchar(45))

    
INSERT INTO @YourTable
    ([GroupID], [GroupName], [MenuItemID], [MenuItemName])
VALUES
    (8, 'Cheese', 25, 'Cheese Pizza (VEG)'),
    (8, 'Cheese', 12, 'Bean & Cheese Quesadilla (GF & VEG)'),
    (8, 'Cheese', 13, 'Bean & DF Cheese Quesadilla (GF, VEG, & V)'),
    (8, 'Cheese', 26, 'Cheese Quesadilla (GF & VEG)'),
    (8, 'Cheese', 28, 'Cheese Quesadilla w/ DF Cheese (GF, VEG, & V)'),
    (8, 'Cheese', 27, 'Cheese Quesadilla (VEG)'),
    (8, 'Cheese', 121, 'Vegetable Quesadilla (VEG)'),
    (8, 'Cheese', 122, 'Vegetable Quesadilla w/ DF cheese (VEG & V)'),
    (18, 'Grilled Cheese', 64, 'Grilled Cheese on GF Bread (GF & VEG)'),
    (18, 'Grilled Cheese', 65, 'Grilled Cheese w/ DF Cheese (VEG & V)'),
    (18, 'Grilled Cheese', 63, 'Grilled Cheese (VEG)')


select
      MenuName
from (
        select distinct GroupName, 0 rn, GroupName as MenuName from @YourTable
        union all
        select GroupName, 1, '    ' + MenuItemName from @YourTable
        ) d
order by 
      GroupName
    , rn
    , MenuName

Open in new window

Using this sample: 
declare @YourTable table
    ([GroupID] int, [GroupName] varchar(14), [MenuItemID] int, [MenuItemName] varchar(45))

    
INSERT INTO @YourTable
    ([GroupID], [GroupName], [MenuItemID], [MenuItemName])
VALUES
    (8, 'Cheese', 25, 'Cheese Pizza (VEG)'),
    (8, 'Cheese', 12, 'Bean & Cheese Quesadilla (GF & VEG)'),
    (8, 'Cheese', 13, 'Bean & DF Cheese Quesadilla (GF, VEG, & V)'),
    (8, 'Cheese', 26, 'Cheese Quesadilla (GF & VEG)'),
    (8, 'Cheese', 28, 'Cheese Quesadilla w/ DF Cheese (GF, VEG, & V)'),
    (8, 'Cheese', 27, 'Cheese Quesadilla (VEG)'),
    (8, 'Cheese', 121, 'Vegetable Quesadilla (VEG)'),
    (8, 'Cheese', 122, 'Vegetable Quesadilla w/ DF cheese (VEG & V)'),
    (18, 'Grilled Cheese', 64, 'Grilled Cheese on GF Bread (GF & VEG)'),
    (18, 'Grilled Cheese', 65, 'Grilled Cheese w/ DF Cheese (VEG & V)'),
    (18, 'Grilled Cheese', 63, 'Grilled Cheese (VEG)')

Open in new window

the query above produced this result: 
 	MenuName
	Cheese
	    Bean & Cheese Quesadilla (GF & VEG)
	    Bean & DF Cheese Quesadilla (GF, VEG, & V)
	    Cheese Pizza (VEG)
	    Cheese Quesadilla (GF & VEG)
	    Cheese Quesadilla (VEG)
	    Cheese Quesadilla w/ DF Cheese (GF, VEG, & V)
	    Vegetable Quesadilla (VEG)
	    Vegetable Quesadilla w/ DF cheese (VEG & V)
	Grilled Cheese
	    Grilled Cheese (VEG)
	    Grilled Cheese on GF Bread (GF & VEG)
	    Grilled Cheese w/ DF Cheese (VEG & V)

Open in new window

RadhaKrishnaKiJaya

Author

Commented:
Thanks a lot!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial