DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
 
 
SITE SEARCH SQLUSA.com VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE