datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to form a list with XML PATH & text()?

Execute the following Microsoft T-SQL script in SSMS Query Editor to form a comma-separated list of product colors by subcategory for AdventureWorks Cycles:

USE ADVENTUREWORKS;

 

SELECT   Subcategory = ps.Name,

         Colors = STUFF((-- Form a comma delimited list

                        SELECT DISTINCT [text()] = ', ' + CONVERT(VARCHAR,Color)

                         FROM   Production.Product

                         WHERE  ProductSubcategoryID = ps.ProductSubcategoryID

                                AND Color IS NOT NULL

                         FOR XML PATH ('')

                        -- End: Form a comma delimited list

                        ),1,1,'')

FROM     Production.Product p

         INNER JOIN Production.ProductSubcategory ps

           ON ps.ProductSubcategoryID = p.ProductSubcategoryID

WHERE    Color IS NOT NULL

GROUP BY ps.Name,

         ps.ProductSubcategoryID

ORDER BY Subcategory

 

Results

Subcategory Colors
Bib-Shorts  Multi
Brakes  Silver
Caps  Multi
Chains  Silver
Cranksets  Black
Derailleurs  Silver
Gloves  Black
Helmets  Black, Blue, Red
Hydration Packs  Silver
Jerseys  Multi, Yellow
Mountain Bikes  Black, Silver
Mountain Frames  Black, Silver
Panniers  Grey
Pedals  Silver/Black
Road Bikes  Black, Red, Yellow
Road Frames  Black, Red, Yellow
Shorts  Black
Socks  White
Tights  Black
Touring Bikes  Blue, Yellow
Touring Frames  Blue, Yellow
Vests  Blue
Wheels  Black

 

 

 

 

Exam Prep 70-461
Exam 70-461