For XML Path application merge result rows horizontally using an SQL statement

Result set before merging:

Id FTYPE fname
1 fruit Apple
2 Fruit Bananas
3 fruit Sydney
4. Fruit Watermelon
5. Fruit Lychee
6 fruit Grapefruit
7. transport buses
8. Transportation Subway
9 transport trains
10 Transportation trains
11. Transportation Vehicles
12 transportation ships
13 animal kitten
14 animal puppies
15 animal rabbits
16 animal birds

 

Merged result set:

FTYPE fname
Animals, cats, puppies, rabbits, birds
Transportation buses, subways, motor trains, trains, automobiles, and ships
Fruit apples, bananas, Sydney, watermelon, Lychee, grapefruit

 

Step 1: Create a test environment

 If     Exists (  Select     *     From  Sysobjects  Where  ID  =     Object_id  (  '  [Tbfruit]  '  )  And    Objectproperty  (ID,  '  Isusertable  '  )  =     1  )
  Drop     Table     [  Tbfruit ]  

  Create     Table     [  Tbfruit  ]  (
ID  Int    Identity  (  1  ,  1 )  Not     Null  ,
FTYPE  Nvarchar  (  50  )  Null  ,
Fname  Nvarchar  (  50  )  Null  
)


 Insert     [  Tbfruit  ]  (FTYPE,  [  Fname  ]  )  Values  (  '  Fruits  '  ,  '  Apple '  )
  Insert     [  Tbfruit  ]  (FTYPE,  [  Fname  ]  )  Values  (  '  Fruits  '  , '  Bananas  '  )
  Insert     [  Tbfruit  ]  (FTYPE,  [  Fname  ]  )  Values  (  '  Fruits '  ,  '  Sydney  '  )
  Insert     [  Tbfruit  ]  (FTYPE,  [  Fname  ]  )  Values  ( '  Fruits  '  ,  '  Watermelon  '  )
  Insert     [  Tbfruit  ]  (FTYPE,  [  Fname  ]  ) Values  (  '  Fruits  '  ,  '  Lychee  '  )
  Insert     [  Tbfruit  ]  (FTYPE,  [  Fname ]  )  Values  (  '  Fruits  '  ,  '  Grapefruit  '  )

  Insert     [  Tbfruit  ]  (FTYPE, [  Fname  ]  )  Values  (  '  Transportation  '  ,  '  Bus  '  )
  Insert     [  Tbfruit ]  (FTYPE,  [  Fname  ]  )  Values  (  '  Transportation  '  ,  '  Subway  '  )
  Insert    [  Tbfruit  ]  (FTYPE,  [  Fname  ]  )  Values  (  '  Transportation  '  ,  '  EMU  '  )
 Insert     [  Tbfruit  ]  (FTYPE,  [  Fname  ]  )  Values  (  '  Transportation  '  ,  '  Train '  )
  Insert     [  Tbfruit  ]  (FTYPE,  [  Fname  ]  )  Values  (  '  Transportation  '  , '  Automobile  '  )
  Insert     [  Tbfruit  ]  (FTYPE,  [  Fname  ]  )  Values  (  '  Transportation '  ,  '  Ship  '  )

  Insert     [  Tbfruit  ]  (FTYPE,  [  Fname  ]  )  Values  ( '  Animals  '  ,  '  Kitten  '  )
  Insert     [  Tbfruit  ]  (FTYPE,  [  Fname  ]  ) Values  (  '  Animals  '  ,  '  Puppy  '  )
  Insert     [  Tbfruit  ]  (FTYPE,  [  Fname ]  )  Values  (  '  Animals  '  ,  '  Rabbit  '  )
  Insert     [  Tbfruit  ]  (FTYPE, [  Fname  ]  )  Values  (  '  Animals  '  ,  '  Birdie  '  )

  Select     *    From     [  Tbfruit  ] 

Step 2: Get Results

 Select  B. FTYPE,  Left  (Fname,  Len  (Fname)  -  1  )  As  Fname  From (
  Select     Distinct  FTYPE,
(  Select  Fname  +  '  ,  '     From  Tbfruit  Where  FTYPE  = A. FTYPE  For  XML Path (  ''  ))  As  Fname
  From  Tbfruit
) B