String Concat, String Group By

커뮤니티에 약간 자주 String Concat, String Group By가 올라와서..,

 

USE AdventureWorks

GO

 

SELECT c.Name ID,  s.Name 

INTO #t

 FROM Production.ProductCategory c

JOIN Production.ProductSubcategory s ON c.ProductCategoryID=s.ProductCategoryID

 

 

SELECT * FROM #t

 

Bikes Mountain Bikes

Bikes Road Bikes

Bikes Touring Bikes

Components Handlebars

Components Bottom Brackets

Components Brakes

Components Chains

Components Cranksets

Components Derailleurs

Components Forks

Components Headsets

Components Mountain Frames

…..,

 

 

SELECT ID, STUFF(

     (SELECT  ',' + a.Name  AS [text()]

     from #t  a

     where a.ID = b.ID

     Order by a.Name

     for xml PATH('')),1,1,''   ) AS r

 from #t b

group by ID

ORDER BY ID