Lions Den

The Code and Times of Hanan Schwartzberg

About Hanan | Hanan's CV | Contact Hanan

December 24, 2017

How to Concatenate Rows in SQL

Filed under: SQL Server — Tags: , , — Hanan Schwartzberg @ 4:12 pm

The problem

You need to concatenate the values from a field across several rows into a single field as part of a query. For instance, if you need to pull an item’s name from one table with a list of keywords from another. At the same time any blank value need to be removed.

The Solution

SELECT I.Name,
	stuff( (SELECT (CASE IK.Keyword WHEN '' THEN NULL ELSE ',' + IK.Keyword END) 
        FROM ItemKeyword IK
        WHERE IK.ItemID = I.ItemID
        ORDER BY IK.Keyword
        FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
    ,1,1,'')
FROM Item I
Home | Site Design | Banner Design | Code Den | Offsite Posts | Downloads | Photography | About Hanan | Hanan's CV | Contact Hanan
Copyright © 2009 Hanan Schwartzberg. All rights reserved.