.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Code Snippets >> Bugs & Resolutions >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

A Grouping Query

Posted By:Sajid P K       Posted Date: October 12, 2010    Points: 15    Category: Bugs & Resolutions    URL: http://www.dotnetspark.com  

This Article explains a simple Grouping Query
 

 

We have a table like following.

 

Col 1           Col 2 
 X               A1  
 X               A2  
 X               B1  
 X               B2  
 Y               C1 
 Y               C2 
 Y               B3 
 Y               B4 
 Y               A4

We have to Write TSQL statement, which will return the result as shown below.

 

 Col 1       Col 2
  X         A1, A2, B1,B2 
  Y         C1, C2, B3, B4, A4

Answer

 

declare @sample table 
     (  row   varchar(10), 
        string  varchar(10)
     )
 insert into @sample
 select  'X', 'A1 '  
  union all
 select  'X', 'A2 '  
  union all
 select  'X', 'B2 '  
  union all
 select  'X', 'B2'  
  union all
 select  'Y', 'C1'  
  union all
 select  'Y', 'C2'  
  union all
 select  'Y', 'B3' 
  union all
 select  'Y', 'B4' 
  union all
 select  'Y', 'A4'  


select  row,   STUFF((SELECT ', ' + x.string    
   FROM   @sample x    
   WHERE   x.row = s.row FOR XML PATH('')) , 1, 1, '') from  @sample Sgroup by row


 


     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More code samples in C#, ASP.Net, Vb.Net and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend